34,333
edits
Changes
→Performing Calculations on Retrieved Data
<td>/<td>Divide</td>
</table>
== Concatenating Data Fields ==
Similar steps can be taken to concatenate the text values (i.e join them together by appending them together) retrieved from two or more table columns during data retrieval. This is achieved using the MySQL ''Concat()'' function. For example, suppose we need to append the supplier_name and supplier address fields from the following table:
<pre>
+-------------+-----------------+------------------------+------------------+
| supplier_id | supplier_name | supplier_address | supplier_contact |
+-------------+-----------------+------------------------+------------------+
| 1 | Microsoft | 1 Microsoft Way | Bill Gates |
| 2 | Apple, Inc. | 1 Infinate Loop | Steve Jobs |
| 3 | EasyTech | 100 Beltway Drive | John Williams |
| 4 | WildTech | 100 Hard Drive | Alan Wilkes |
| 5 | Hewlett Packard | 100 Printer Expressway | Dave Packard |
+-------------+-----------------+------------------------+------------------+
</pre>
To do so, we use the Concat() function in the SELECT statement:
<pre>SELECT Concat(supplier_name, supplier_address) FROM suppliers;
</pre>
Unfortunately this doesn't quite give us what we need because it doesn't put a space between the supplier_name and supplier_address fields:
<pre>
+-----------------------------------------+
| Concat(supplier_name, supplier_address) |
+-----------------------------------------+
| Microsoft1 Microsoft Way |
| Apple, Inc.1 Infinate Loop |
| EasyTech100 Beltway Drive |
| WildTech100 Hard Drive |
| Hewlett Packard100 Printer Expressway |
+-----------------------------------------+
5 rows in set (0.02 sec)
</pre>
Fortunately, we can put anything we like in the Concat statement. For example we can add a space between the two fields:
<pre>
SELECT Concat(supplier_name, ' ', supplier_address) FROM suppliers;
+----------------------------------------------+
| Concat(supplier_name, ' ', supplier_address) |
+----------------------------------------------+
| Microsoft 1 Microsoft Way |
| Apple, Inc. 1 Infinate Loop |
| EasyTech 100 Beltway Drive |
| WildTech 100 Hard Drive |
| Hewlett Packard 100 Printer Expressway |
+----------------------------------------------+
5 rows in set (0.00 sec)
</pre>
Or we can put in some text to tell us what the fields are:
<pre>
SELECT Concat('Name: ', supplier_name, ' ', ' Address: ', supplier_address) FROM suppliers;
+----------------------------------------------------------------------+
| Concat('Name: ', supplier_name, ' ', ' Address: ', supplier_address) |
+----------------------------------------------------------------------+
| Name: Microsoft Address: 1 Microsoft Way |
| Name: Apple, Inc. Address: 1 Infinate Loop |
| Name: EasyTech Address: 100 Beltway Drive |
| Name: WildTech Address: 100 Hard Drive |
| Name: Hewlett Packard Address: 100 Printer Expressway |
+----------------------------------------------------------------------+
5 rows in set (0.00 sec)
</pre>
== Trimming Whitespace from Text ==
MySQL also provide a technique for remove any trailing whitespace from a text field. Say for example a data input operatior pressed the space bar for a few seconds after entering a company name into our supplier database. When this is retrieved the spaces will also be retrieved:
<pre>
SELECT Concat('Name: ', supplier_name, ' ', ' Address: ', supplier_address) FROM suppliers; +----------------------------------------------------------------------+
| Concat('Name: ', supplier_name, ' ', ' Address: ', supplier_address) |
+----------------------------------------------------------------------+
| Name: Microsoft Address: 1 Microsoft Way |
| Name: Apple, Inc. Address: 1 Infinate Loop |
| Name: EasyTech Address: 100 Beltway Drive |
| Name: WildTech Address: 100 Hard Drive |
| Name: Hewlett Packard Address: 100 Printer Expressway |
| Name: IBM Address: 100 West Haven |
+----------------------------------------------------------------------+
6 rows in set (0.00 sec)
</pre>
Clearly, the trailing spaces in the IBM row are causing us formatting problems. To remove these spaces we can use the ''RTrim()'' function. RTrim() removes any training spaces to the right of text value and can be used as follows:
<pre>
SELECT Concat('Name: ', RTrim(supplier_name), ' ', ' Address: ', supplier_address) FROM suppliers;
+-----------------------------------------------------------------------------+
| Concat('Name: ', RTrim(supplier_name), ' ', ' Address: ', supplier_address) |
+-----------------------------------------------------------------------------+
| Name: Microsoft Address: 1 Microsoft Way |
| Name: Apple, Inc. Address: 1 Infinate Loop |
| Name: EasyTech Address: 100 Beltway Drive |
| Name: WildTech Address: 100 Hard Drive |
| Name: Hewlett Packard Address: 100 Printer Expressway |
| Name: IBM Address: 100 West Haven |
+-----------------------------------------------------------------------------+
</pre>
