Regular Expression
MySQL's regular expression support is built on the International
Components for Unicode (ICU) regular expression library, offering
full Unicode-aware pattern matching. REGEXP and RLIKE
(see Operators) are synonyms for
REGEXP_LIKE(); the functions below extend that support with
position, replacement, and extraction.
REGEXP_LIKE()
| REGEXP_LIKE(expr, pattern [, match_type]) |
Returns 1 if the string expr matches the pattern, 0
otherwise. An optional match_type string of flag characters (e.g.
'i' for case-insensitive, 'c' for case-sensitive) can override the
collation's default case sensitivity, and a COLLATE clause on
either argument has the same effect.
SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs); |
1 (default collation is case-insensitive); 0 (the _as_cs
collation is case-sensitive).
SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs);
SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs);
SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE'):
| REGEXP_LIKE('CamelCase', 'CAMELCASE') |
|---|
| 1 |
SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs):
| REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs) |
|---|
| 0 |
REGEXP_INSTR()
| REGEXP_INSTR(expr, pattern [, pos [, occurrence [, return_option [, match_type]]]]) |
Returns the 1-based position of the first (or occurrence-th)
match, searching from character pos, or 0 if there is no match.
| SELECT REGEXP_INSTR('dog cat dog', 'dog', 2); |
9 — searching from position 2 skips the "dog" that
starts at position 1 and finds the one starting at position 9.
| SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}'); |
8 — the only run of four or more consecutive "a"
characters starts at position 8.
SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
SELECT REGEXP_INSTR('dog cat dog', 'dog', 2):
| REGEXP_INSTR('dog cat dog', 'dog', 2) |
|---|
| 9 |
SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}'):
| REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
|---|
| 8 |
REGEXP_REPLACE()
| REGEXP_REPLACE(expr, pattern, repl [, pos [, occurrence [, match_type]]]) |
Replaces occurrences of pattern in expr with repl, and
returns the resulting string. By default every match is replaced;
supplying occurrence replaces only that specific match.
| SELECT REGEXP_REPLACE('a b c', 'b', 'X'); |
'a X c'
| SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3); |
'abc def X' — only the 3rd match ("ghi") is replaced.
SELECT REGEXP_REPLACE('a b c', 'b', 'X');
SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
SELECT REGEXP_REPLACE('a b c', 'b', 'X');
SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
SELECT REGEXP_REPLACE('a b c', 'b', 'X'):
| REGEXP_REPLACE('a b c', 'b', 'X') |
|---|
| a X c |
SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3):
| REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) |
|---|
| abc def X |
REGEXP_SUBSTR()
| REGEXP_SUBSTR(expr, pattern [, pos [, occurrence [, match_type]]]) |
Returns the substring of expr that matches pattern, or NULL
if there is no match.
| SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+'); |
'abc' — the first match.
| SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3); |
'ghi' — the 3rd match, searching from position 1.
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+'):
| REGEXP_SUBSTR('abc def ghi', '[a-z]+') |
|---|
| abc |
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3):
| REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |
|---|
| ghi |