MariaDB Crash Course - Ben Forta [28]
Actually, it could also have been written as
Input
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9]{4}'
ORDER BY prod_name;
Anchors
All the examples thus far have matched text anywhere within a string. To match text at specific locations, you need to use anchors as listed in Table 9.4.
Table 9.4 Anchor Metacharacters
For example, what if you wanted to find all products that started with a number (including numbers starting with a decimal point)? A simple search for [0-9\\.] (or [[:digit:]\\.]) would not work because it would find matches anywhere within the text. The solution is to use the ^ anchor, as seen here:
Input
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;
Output
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
Analysis
^ matches the start of a string. As such, ^[0-9\\.] matches . or any digit only if they are the first characters within a string. Without the ^, four other rows would have been retrieved, too (those that have digits in the middle).
* * *
Note: The Dual Purpose ^
^ has two uses. Within a set (defined using [ and ]) it is used to negate that set. Otherwise, it is used to refer to the start of a string.
* * *
* * *
Note: Making REGEXP Behave like LIKE
Earlier in this chapter I mentioned that LIKE and REGEXP behaved differently in that LIKE matched an entire string and REGEXP matched substrings, too. Using anchors, REGEXP can be made to behave just like LIKE by simply starting each expression with ^ and ending it with $.
* * *
* * *
Tip: Simple Regular Expression Testing
You can use SELECT to test regular expressions without using database tables. REGEXP checks always return 0 (not a match) or 1 (match). You can use REGEXP with literal strings to test expressions and to experiment with them. The syntax would look like this:
SELECT 'hello' REGEXP '[0-9]';
This example would obviously return 0 (as there are no digits in the text hello).
* * *
Summary
In this chapter, you learned the basics of regular expressions and how to use them in MariaDB SELECT statements via the REGEXP keyword.
10. Creating Calculated Fields
In this chapter, you learn what calculated fields are, how to create them, and how to use aliases to refer to them from within your application.
Understanding Calculated Fields
Data stored within a database’s tables is often not available in the exact format needed by your applications. Here are some examples:
• You need to display a field containing the name of a company along with the company’s location, but that information is stored in separate table columns.
• City, state, and ZIP Code are stored in separate columns (as they should be), but your mailing label printing program needs them retrieved as one correctly formatted field.
• Column data is in mixed upper- and lowercase, and your report needs all data presented in uppercase.
• An order items table stores item price and quantity but not the expanded price (price multiplied by quantity) of each item. To print invoices, you need that expanded price.
• You need total, averages, or other calculations based on table data.
In each of these examples, the data stored in the table is not exactly what your application needs. Rather than retrieve the data as it is and then reformat it within your client application or report, what you really want is to retrieve converted, calculated, or reformatted data directly from the database.
This is where calculated fields come in. Unlike all the columns we retrieved in the chapters thus far, calculated fields don’t actually exist in database tables. Rather, a calculated field is created on-the-fly within a SQL SELECT statement.
* * *
New Term: Field
Essentially means the same thing as column and often is used interchangeably, although database columns are typically called columns and the term fields is normally used in conjunction with calculated fields.
* * *
It is important to note