May 16, 2021 MariaDB
In addition to pattern matching provided by theLIKE clause, MariaDB provides regular expression-based matching through the REGEXP operator. T he operator performs a pattern match on a string expression based on a given pattern.
MariaDB 10.0.5 introduces PCRE Regular Expressions, which greatly increases the range of matches, such as recursive patterns, forward-looking assertions, and so on.
See the use of the standard REGEXP operator syntax given below -
SELECT column FROM table_name WHERE column REGEXP '[PATTERN]';
REGEXP returns 1 for pattern matching, or 0 for no pattern match.
The opposite option exists in the form of NOT REGEXP. M ariaDB also provides reGEXP and NOT REGEXP, RLIKE and NOT RLIKE synonyms, which were created for compatibility reasons.
The pattern of the comparison can be a text string or something else, such as a table column. I n a string, it uses the C escape syntax, so double any """"" R EGEXP is also case insensi now, except for binary strings.
Below is a table of possible patterns that can be used -
| S.No | Patterns and instructions |
|---|---|
| 1 |
^ It matches the beginning of the string. |
| 2 |
$ It matches the end of the string. |
| 3 |
. It matches a single character. |
| 4 |
[...] It matches any character in parentheses. |
| 5 |
[^ ...] It matches any characters that are not listed in parentheses. |
| 6 |
P1 | P 2 | P3 It matches any pattern. |
| 7 |
* It matches 0 or more instances of the preceding element. |
| 8 |
+ It matches one or more instances of the previous element. |
| 9 |
{N} It matches n instances of the previous element. |
| 10 |
{M，N} It matches n instances from m to the previous element. |
Check out the pattern matching example given below -
Products that
start with "pr"
-
SELECT name FROM product_tbl WHERE name REGEXP '^pr';
Products ending with "na" -
SELECT name FROM product_tbl WHERE name REGEXP 'na$';
The product that begins with a phonetic tone -
SELECT name FROM product_tbl WHERE name REGEXP '^[aeiou]';