Concatenating Fields


To demonstrate working with calculated fields, let's start with a simple examplecreating a title made up of two columns.

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?

New Term

Concatenate Joining values together (by appending them to each other) to form a single long value.


The solution is to concatenate the two columns. In MySQL SELECT statements, you can concatenate columns using the Concat() function.

Tip

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.


Input

SELECT Concat(vend_name, ' (', vend_country, ')') FROM vendors ORDER BY vend_name;

Output

+--------------------------------------------+ | Concat(vend_name, ' (', vend_country, ')') | +--------------------------------------------+ | ACME (USA)                                 | | Anvils R Us (USA)                          | | Furball Inc. (USA)                         | | Jet Set (England)                          | | Jouets Et Ours (France)                    | | LT Supplies (USA)                          | +--------------------------------------------+

Analysis

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_name column

  • A string containing a space and an open parenthesis

  • The state stored in the vend_country column

  • 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 Chapter 8, "Using Wildcard Filtering," 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:

Input

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') FROM vendors ORDER BY vend_name;

Analysis

The RTrim() function trims all spaces from the right of a value. By using RTrim(), the individual columns are all trimmed properly.

Note

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


Using 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:

Input

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

Output

+-------------------------+ | vend_title              | +-------------------------+ | ACME (USA)              | | Anvils R Us (USA)       | | Furball Inc. (USA)      | | Jet Set (England)       | | Jouets Et Ours (France) | | LT Supplies (USA)       | +-------------------------+

Analysis

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.

Tip

Other Uses for Aliases 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.


Note

Derived Columns Aliases are also sometimes referred to as derived columns, so regardless of the term you run across, they mean the same thing.





MySQL Crash Course
MySQL Crash Course
ISBN: 0672327120
EAN: 2147483647
Year: 2004
Pages: 214
Authors: Ben Forta

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net