SQL & Regular Expressions
-
REGEXP & RLIKE
To use regular expressions in SQL, the following functions are most commonly used:
| Name | Description |
|---|---|
| REGEXP | Whether string matches regular expression |
| RLIKE | Whether string matches regular expression |
| NOT REGEXP | Negation of REGEXP |
| NOT RLIKE | Negation of RLIKE |
-
SQL Regular Expression Syntax
Regular Expression Operators
[expression] NOT REGEXP [pattern]
[expression] NOT RLIKE [pattern]
equivalent to:
NOT ([expression] REGEXP [pattern])
-
SQL Regular Expression Syntax (continued)
expression REGEXP pattern
expression RLIKE pattern
Both return 1 (true) if the expression matches the regular expression specified by the pattern, 0 (false) if otherwise. If either the expression or pattern is NULL, the return value will be NULL.
RLIKE is a synonym for REGEXP.
-
A bit of info:
Since MySQL uses the C escape syntax in strings (for example, \n to represent the newline character), you must double any ** that you use in your **REGEXP arguments.
-
SQL Regular Expression Syntax (continued)
mysql> SELECT 'Michael!' REGEXP '.*';
+------------------------+
| 'Michael!' REGEXP '.*' |
+------------------------+
| 1 |
+------------------------+
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
+---------------------------------------+
| 'new*\n*line' REGEXP 'new\\*.\\*line' |
+---------------------------------------+
| 0 |
+---------------------------------------+
-
SQL Regular Expression Syntax (continued)
mysql> SELECT 'a' REGEXP '^[a-d]';
+---------------------+
| 'a' REGEXP '^[a-d]' |
+---------------------+
| 1 |
+---------------------+
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
+----------------+-----------------------+
| 'a' REGEXP 'A' | 'a' REGEXP BINARY 'A' |
+----------------+-----------------------+
| 1 | 0 |
+----------------+-----------------------+
-
Review of RegEx: examples
^
Match the beginning of a string.
mysql> SELECT 'fo\nfo' REGEXP '^fo$';
returns: 0
mysql> SELECT 'fofo' REGEXP '^fo';
returns: 1
-
Review of RegEx: examples (continued)
$
Match the end of a string.
mysql> SELECT 'fo\no' REGEXP '^fo\no$';
returns: 1
mysql> SELECT 'fo\no' REGEXP '^fo$';
returns: 0
-
Review of RegEx: examples (continued)
.
Match any character (including carriage return and newline).
mysql> SELECT 'fofo' REGEXP '^f.*$';
returns: 1
mysql> SELECT 'fo\r\nfo' REGEXP '^f.*$';
returns: 1
-
Review of RegEx: examples (continued)
a*
Match any sequence of zero or more a characters.
mysql> SELECT 'Ban' REGEXP '^Ba*n';
returns: 1
mysql> SELECT 'Baaan' REGEXP '^Ba*n';
returns: 1
mysql> SELECT 'Bn' REGEXP '^Ba*n';
returns: 1
-
Review of RegEx: examples (continued)
a+
Match any sequence of one or more a characters.
mysql> SELECT 'Ban' REGEXP '^Ba+n';
returns: 1
mysql> SELECT 'Bn' REGEXP '^Ba+n';
returns: 0
-
Review of RegEx: examples (continued)
a?
Match either zero or one a character.
mysql> SELECT 'Bn' REGEXP '^Ba?n';
returns: 1
mysql> SELECT 'Ban' REGEXP '^Ba?n';
returns: 1
mysql> SELECT 'Baan' REGEXP '^Ba?n';
returns: 0
-
Review of RegEx: examples (continued)
| **firstpattern | secondpattern** |
Alternation; match either of the sequences firstpattern or secondpattern.
mysql> SELECT 'pi' REGEXP 'pi|apa';
returns: 1
mysql> SELECT 'axe' REGEXP 'pi|apa';
returns: 0
mysql> SELECT 'apa' REGEXP 'pi|apa';
returns: 1
-
Review of RegEx: examples (continued)
mysql> SELECT 'apa' REGEXP '^(pi|apa)$';
returns: 1
mysql> SELECT 'pi' REGEXP '^(pi|apa)$';
returns: 1
mysql> SELECT 'pix' REGEXP '^(pi|apa)$';
returns: 0
-
Review of RegEx: examples (continued)
(abc)*
Match zero or more instances of the sequence abc.
mysql> SELECT 'pi' REGEXP '^(pi)*$';
returns: 1
mysql> SELECT 'pip' REGEXP '^(pi)*$';
returns: 0
mysql> SELECT 'pipi' REGEXP '^(pi)*$';
returns: 1
-
Review of RegEx: examples (continued)
{1}, {2,3}
Repetition; {n} and {m,n} notation provide a more general way of writing regular expressions that match many occurrences of the previous part of the pattern. m and n are integers.
a*
Can be written as a{0,}.
a+
Can be written as a{1,}.
a?
Can be written as a{0,1}.
-
Review of RegEx: examples (continued)
To be more precise, a{n} matches exactly n instances of a. a{n,} matches n or more instances of a. a{m,n} matches m through n instances of a, inclusive. If both m and n are given, m must be less than or equal to n.
mysql> SELECT 'abcde' REGEXP 'a[bcd]{2}e';
returns: 0
mysql> SELECT 'abcde' REGEXP 'a[bcd]{3}e';
returns: 1
mysql> SELECT 'abcde' REGEXP 'a[bcd]{1,10}e';
returns: 1
-
Review of RegEx: examples (continued)
[a-dX], [^a-dX]
Matches or negates the character class match.
mysql> SELECT 'aXbc' REGEXP '[a-dXYZ]';
returns: 1
mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]$';
returns: 0
mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]+$';
returns: 1
-
Review of RegEx: examples (continued)
mysql> SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$';
returns: 0
mysql> SELECT 'gheis' REGEXP '^[^a-dXYZ]+$';
returns: 1
mysql> SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$';
returns: 0
-
Special Characters
To use a literal instance of a special character in a regular expression, precede it by two backslash (**) characters. The MySQL parser interprets one of the backslashes, and the regEx library interprets the other.
For example, to match the string Cliff-jumper that contains the special - character, you would use the following syntax:
mysql> SELECT 'Cliff-jumper' REGEXP 'Cliff\\-jumper';
-
Using REGEXP in WHERE clauses
To use REGEXP to refine your WHERE clause, use the following syntax:
mysql> SELECT column1, column1 FROM table_name WHERE matching_string REGEXP pattern;
For example:
mysql> SELECT id, title FROM poems WHERE body REGEXP '^\\'Twas';
https://dev.mysql.com/doc/refman/5.7/en/regexp.html
-
