MySQL Calculations and Concatenations
| Previous | Table of Contents | Next |
| An Introduction to MySQL Views | Manipulating Text in MySQL |
| Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99
|
When data 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 text string, but in reality the name and address are held in separate tables. Similarly, a table might contain the price for an 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 let's 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)
MySQL supports a number of basic mathematical operators:
| Operator | Description |
|---|---|
| + | Add |
| - | Subtract |
| * | Multiplication |
| / | Divide |
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 Infinite 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 Infinite 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 Infinite 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 Infinite 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 Trailing Whitespace from Text
MySQL also provide a technique for remove any trailing whitespace from a text field. Say for example a data input operator 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 Infinite 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 Infinite 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 |
+-----------------------------------------------------------------------------+
Trimming Leading Whitespace from Text
The LTrim() function may be used in the same way to remove leading spaces from the left hand side of a text value.
| Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99
|
| Previous | Table of Contents | Next |
| An Introduction to MySQL Views | Manipulating Text in MySQL |
Bold text
