| Metacharacter | Description |
|---|---|
|
|
Start of text |
|
|
End of text |
|
|
Start of word |
|
|
End of word |
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:
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;
The above statement displayed the following result:
+------------+ | prod_name | +------------+ | .2 product | | 1 product | | 2 product | +------------+
^ 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).
^ 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.
Making REGEXP Behave Like LIKE Earlier in this tutorial 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 $.
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 world' REGEXP '[0-9]';
This example will return 0 as there are no digits in the text hello world.
