Difference between revisions of "MySQL Calculations and Concatenations"

Revision as of 19:23, 22 October 2007

When is retrieved from a MySQL database it is not always in the form we need it. For example, we may need to display a customer name and address as a single test string, but in reality the name and address are held in separate database tables. Similarly, a table might contain the price for a item and the shipping cost in two columns, when what we want is the result of the shipping cost and product cost added together. Fortunately MySQL allows fields retrieved from a table to be concatenated or used in calculations. In this chapter we will look at both approaches to manipulating data as it is extracted from a database.

Performing Calculations on Retrieved Data

Suppose we have a database table called shipping which contains product names, prices and shipping costs. The data contained in such a table might appear as follows:

SELECT * FROM shipping;
+------------+--------------------------+---------------+------------------+
| product_id | product_name             | product_price | product_shipping |
+------------+--------------------------+---------------+------------------+
|          2 | Microsoft 10-20 Keyboard |            25 |                7 |
|          3 | Apple iPhone 8Gb         |           400 |               13 |
|          4 | Dell XPS 400             |           900 |              100 |
+------------+--------------------------+---------------+------------------+
3 rows in set (0.00 sec)

Now lets assume that we need to extract the total cost of a product including the price and the shipping cost. To do so we can perform a calculation based on the product price and product_shipping columns and assign the result to an alias using the AS keyword:

SELECT product_name, product_price+product_shipping AS total_cost FROM shipping;
+--------------------------+------------+
| product_name             | total_cost |
+--------------------------+------------+
| Microsoft 10-20 Keyboard |         32 |
| Apple iPhone 8Gb         |        413 |
| Dell XPS 400             |       1000 |
+--------------------------+------------+
3 rows in set (0.00 sec)

In the above example we have performed a calculation (product_price+product_shipping) and assigned the value to an alias called total_cost. This is then displayed as part of our output.

MySQL supports a number of basic mathematical operators:

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:

+-------------+-----------------+------------------------+------------------+
| 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     |
+-------------+-----------------+------------------------+------------------+

To do so, we use the Concat() function in the SELECT statement:

SELECT Concat(supplier_name, supplier_address) FROM suppliers;

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:

+-----------------------------------------+
| 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)

Fortunately, we can put anything we like in the Concat statement. For example we can add a space between the two fields:

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)

Or we can put in some text to tell us what the fields are:

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)

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:

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)

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:

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                                          |
+-----------------------------------------------------------------------------+