MariaDB Crash Course - Ben Forta [31]
11. Using Data Manipulation Functions
In this chapter, you learn what functions are, what types of functions MariaDB supports, and how to use these functions.
Understanding Functions
Like almost any other computer language, SQL supports the use of functions to manipulate data. Functions are operations usually performed on data, usually to facilitate conversion and manipulation.
An example of a function is the RTrim() that we used in the last chapter to trim any spaces from the end of a string.
* * *
Note: Functions Are Less Portable Than SQL
Code that runs on multiple systems is said to be portable. Most SQL statements are relatively portable, and when differences between SQL implementations do occur they are usually not that difficult to deal with. Functions, on the other hand, tend to be far less portable. Just about every major DBMS supports functions that others don’t, and sometimes the differences are significant.
With code portability in mind, many SQL programmers opt not to use any implementation-specific features. Although this is a somewhat noble and idealistic view, it is not always in the best interests of application performance. If you opt not to use these functions, you make your application code work harder. It must use other methods to do what the DBMS could have done more efficiently.
If you do decide to use functions, make sure you comment your code well, so that at a later date you (or another developer) will know exactly to which SQL implementation you were writing. Code commenting was introduced back in Chapter 4, “Retrieving Data.”
* * *
Using Functions
Most SQL implementations support the following types of functions:
• Text functions are used to manipulate strings of text (for example, trimming or padding values and converting values to upper- and lowercase).
• Numeric functions are used to perform mathematical operations on numeric data (for example, returning absolute numbers and performing algebraic calculations).
• Date and time functions are used to manipulate date and time values and to extract specific components from these values (for example, returning differences between dates and checking date validity).
• System functions return information specific to the DBMS being used (for example, returning user login information or checking version specifics).
Text Manipulation Functions
You’ve already seen an example of text-manipulation functions in the last chapter—the RTrim() function was used to trim white space from the end of a column value. Here is another example, this time using the Upper() function:
Input
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
Output
+----------------+------------------+
| vend_name | vend_name_upcase |
+----------------+------------------+
| ACME | ACME |
| Anvils R Us | ANVILS R US |
| Furball Inc. | FURBALL INC. |
| Jet Set | JET SET |
| Jouets Et Ours | JOUETS ET OURS |
| LT Supplies | LT SUPPLIES |
+----------------+------------------+
Analysis
As you can see, Upper() converts text to uppercase and so in this example each vendor is listed twice, first exactly as stored in the vendors table, and then converted to uppercase as column vend_name_upcase.
Table 11.1 lists some commonly used text-manipulation functions.
Table 11.1 Commonly Used Text-Manipulation Functions
One item in Table 11.1 requires further explanation. SOUNDEX is an algorithm that converts any string of text into an alphanumeric pattern describing the phonetic representation of that text. SOUNDEX takes into account similar sounding characters and syllables, enabling strings to be compared by how they sound rather than how they have been typed. Although SOUNDEX is not a SQL concept, MariaDB (like many other DBMSs) offers SOUNDEX support.
Here’s an example using the Soundex() function. Customer Coyote Inc. is in the customers table and has a contact named