The vendors table contains vendor name and address information. Imagine you are generating a vendor report and need to list the vendor location as part of the vendor name in the format name (location).
The report wants a single value, and the data in the table is stored in two columns: vend_name and vend_country. In addition, you need to surround vend_country with parenthesis, and those are definitely not stored in the database table. The SELECT statement that returns the vendor names and locations is simple enough, but how would you create this combined value?
The solution is to concatenate the two columns. In MySQL SELECT statements, you can concatenate columns using the Concat() function.
MySQL Is Different Most DBMSs use operators + or || for concatenation; MySQL uses the Concat() function. Keep this in mind when converting SQL statements to MySQL.
SELECT Concat(vend_name, ' (', vend_country, ')')
FROM vendors
ORDER BY vend_name;
+--------------------------------------------+
| Concat(vend_name, ' (', vend_country, ')') |
+--------------------------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+--------------------------------------------+
Concat() concatenates strings, appending them to each other to create one bigger string. Concat() requires one or more values to be specified, each separated by commas. The previous SELECT statements concatenate four elements:
-
The name stored in the
vend_namecolumn -
A string containing a space and an open parenthesis
-
The state stored in the
vend_countrycolumn -
A string containing the close parenthesis
As you can see in the output shown previously, the SELECT statement returns a single column (a calculated field) containing all four of these elements as one unit.
Back in previous pages "filter data" I mentioned the need to trim data so as to remove any trailing spaces. This can be done using the MySQL RTrim() function, as follows:
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;
The RTrim() function trims all spaces from the right of a value. By using RTrim(), the individual columns are all trimmed properly.
The trim() Functions In addition to RTrim() (which, as just seen, trims the right side of a string), MySQL supports the use of LTrim() (which trims the left side of a string), and trim() (which trims both the right and left).
Aliases
The SELECT statement used to concatenate the address field works well, as seen in the previous output. But what is the name of this new calculated column? Well, the truth is, it has no name; it is simply a value. Although this can be fine if you are just looking at the results in a SQL query tool, an unnamed column cannot be used within a client application because the client has no way to refer to that column.
To solve this problem, SQL supports column aliases. An alias is just that, an alternative name for a field or value. Aliases are assigned with the AS keyword. Take a look at the following SELECT statement:
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS
vend_title
FROM vendors
ORDER BY vend_name;
+-------------------------+ | vend_title | +-------------------------+ | ACME (USA) | | Anvils R Us (USA) | | Furball Inc. (USA) | | Jet Set (England) | | Jouets Et Ours (France) | | LT Supplies (USA) | +-------------------------+
The SELECT statement itself is the same as the one used in the previous code snippet, except that here the calculated field is followed by the text AS vend_title. This instructs SQL to create a calculated field named vend_title containing the results of the specified calculation. As you can see in the output, the results are the same as before, but the column is now named vend_title and any client application can refer to this column by name, just as it would to any actual table column.
Aliases have other uses, too. Some common uses include renaming a column if the real table column name contains illegal characters (for example, spaces) and expanding column names if the original names are either ambiguous or easily misread.
Aliases are also sometimes referred to as derived columns, so regardless of the term you run across, they mean the same thing.