Regular Expression Function and Operator Descriptions
,expr
NOT REGEXPpat
expr
NOT RLIKEpat
This is the same as
NOT [
.expr
REGEXPpat
]
,expr
REGEXPpat
expr
RLIKEpat
Returns 1 if the string
expr
matches the regular expression specified by the patternpat
, 0 otherwise. Ifexpr
orpat
isNULL
, the return value isNULL
.REGEXP
andRLIKE
are synonyms forREGEXP_LIKE[]
.For additional information about how matching occurs, see the description for
REGEXP_LIKE[]
.mysql> SELECT 'Michael!' REGEXP '.*'; +------------------------+ | 'Michael!' REGEXP '.*' | +------------------------+ | 1 | +------------------------+ mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line'; +---------------------------------------+ | 'new*\n*line' REGEXP 'new\\*.\\*line' | +---------------------------------------+ | 0 | +---------------------------------------+ mysql> SELECT 'a' REGEXP '^[a-d]'; +---------------------+ | 'a' REGEXP '^[a-d]' | +---------------------+ | 1 | +---------------------+
REGEXP_INSTR[
expr
,pat
[,pos
[,occurrence
[,return_option
[,match_type
]]]]]Returns the starting index of the substring of the string
expr
that matches the regular expression specified by the patternpat
, 0 if there is no match. Ifexpr
orpat
isNULL
, the return value isNULL
. Character indexes begin at 1.REGEXP_INSTR[]
takes these optional arguments:pos
: The position inexpr
at which to start the search. If omitted, the default is 1.occurrence
: Which occurrence of a match to search for. If omitted, the default is 1.return_option
: Which type of position to return. If this value is 0,REGEXP_INSTR[]
returns the position of the matched substring's first character. If this value is 1,REGEXP_INSTR[]
returns the position following the matched substring. If omitted, the default is 0.match_type
: A string that specifies how to perform matching. The meaning is as described forREGEXP_LIKE[]
.
For additional information about how matching occurs, see the description for
REGEXP_LIKE[]
.mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
REGEXP_LIKE[
expr
,pat
[,match_type
]]Returns 1 if the string
expr
matches the regular expression specified by the patternpat
, 0 otherwise. Ifexpr
orpat
isNULL
, the return value isNULL
.The pattern can be an extended regular expression, the syntax for which is discussed in Regular Expression Syntax. The pattern need not be a literal string. For example, it can be specified as a string expression or table column.
The optional
match_type
argument is a string that may contain any or all the following characters specifying how to perform matching:c
: Case-sensitive matching.i
: Case-insensitive matching.m
: Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.n
: The.
character matches line terminators. The default is for.
matching to stop at the end of a line.u
: Unix-only line endings. Only the newline character is recognized as a line ending by the.
,^
, and$
match operators.
If characters specifying contradictory options are specified within
match_type
, the rightmost one takes precedence.By default, regular expression operations use the character set and collation of the
expr
andpat
arguments when deciding the type of a character and performing the comparison. If the arguments have different character sets or collations, coercibility rules apply as described in Section 10.8.4, “Collation Coercibility in Expressions”. Arguments may be specified with explicit collation indicators to change comparison behavior.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
match_type
may be specified with thec
ori
characters to override the default case sensitivity. Exception: If either argument is a binary string, the arguments are handled in case-sensitive fashion as binary strings, even ifmatch_type
contains thei
character.Note
MySQL uses C escape syntax in strings [for example,
\n
to represent the newline character]. If you want yourexpr
orpat
argument to contain a literal\
, you must double it. [Unless theNO_BACKSLASH_ESCAPES
SQL mode is enabled, in which case no escape character is used.]mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
mysql> SELECT REGEXP_LIKE['abc', 'ABC']; +---------------------------+ | REGEXP_LIKE['abc', 'ABC'] | +---------------------------+ | 1 | +---------------------------+ mysql> SELECT REGEXP_LIKE['abc', 'ABC', 'c']; +--------------------------------+ | REGEXP_LIKE['abc', 'ABC', 'c'] | +--------------------------------+ | 0 | +--------------------------------+
REGEXP_REPLACE[
expr
,pat
,repl
[,pos
[,occurrence
[,match_type
]]]]Replaces occurrences in the string
expr
that match the regular expression specified by the patternpat
with the replacement stringrepl
, and returns the resulting string. Ifexpr
,pat
, orrepl
isNULL
, the return value isNULL
.REGEXP_REPLACE[]
takes these optional arguments:pos
: The position inexpr
at which to start the search. If omitted, the default is 1.occurrence
: Which occurrence of a match to replace. If omitted, the default is 0 [which means “replace all occurrences”].match_type
: A string that specifies how to perform matching. The meaning is as described forREGEXP_LIKE[]
.
Prior to MySQL 8.0.17, the result returned by this function used the
UTF-16
character set; in MySQL 8.0.17 and later, the character set and collation of the expression searched for matches is used. [Bug #94203, Bug #29308212]For additional information about how matching occurs, see the description for
REGEXP_LIKE[]
.mysql> SELECT REGEXP_REPLACE['a b c', 'b', 'X']; +-----------------------------------+ | REGEXP_REPLACE['a b c', 'b', 'X'] | +-----------------------------------+ | a X c | +-----------------------------------+ mysql> 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[
expr
,pat
[,pos
[,occurrence
[,match_type
]]]]Returns the substring of the string
expr
that matches the regular expression specified by the patternpat
,NULL
if there is no match. Ifexpr
orpat
isNULL
, the return value isNULL
.REGEXP_SUBSTR[]
takes these optional arguments:pos
: The position inexpr
at which to start the search. If omitted, the default is 1.occurrence
: Which occurrence of a match to search for. If omitted, the default is 1.match_type
: A string that specifies how to perform matching. The meaning is as described forREGEXP_LIKE[]
.
Prior to MySQL 8.0.17, the result returned by this function used the
UTF-16
character set; in MySQL 8.0.17 and later, the character set and collation of the expression searched for matches is used. [Bug #94203, Bug #29308212]For additional information about how matching occurs, see the description for
REGEXP_LIKE[]
.mysql> SELECT REGEXP_SUBSTR['abc def ghi', '[a-z]+']; +----------------------------------------+ | REGEXP_SUBSTR['abc def ghi', '[a-z]+'] | +----------------------------------------+ | abc | +----------------------------------------+ mysql> SELECT REGEXP_SUBSTR['abc def ghi', '[a-z]+', 1, 3]; +----------------------------------------------+ | REGEXP_SUBSTR['abc def ghi', '[a-z]+', 1, 3] | +----------------------------------------------+ | ghi | +----------------------------------------------+