MariaDB Crash Course - Ben Forta [29]
* * *
Tip: Client Versus Server Formatting
Many of the conversions and reformatting that can be performed within SQL statements can also be performed directly in your client application. However, as a rule, it is far quicker to perform these operations on the database server than it is to perform them within the client because DBMSs are built to perform this type of processing quickly and efficiently.
* * *
Concatenating Fields
To demonstrate working with calculated fields, let’s start with a simple example—creating 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 parentheses, 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 MariaDB SELECT statements, you can concatenate columns using the Concat() function.
* * *
Tip: MariaDB Is Different
Most DBMSs use operators + or || for concatenation; MariaDB (like MySQL) uses the Concat() function. Keep this in mind when converting SQL statements to MariaDB (and 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 MariaDB 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), MariaDB 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