MySQL :: MySQL 8.0 Reference Manual :: 14.8.2 Regular Expressions (original) (raw)

Regular Expression Function and Operator Descriptions

• expr NOT REGEXP pat,expr NOT RLIKE pat
  This is the same as NOT (_`expr`_ REGEXP_`pat`_).
• expr REGEXP pat,expr RLIKE pat
  Returns 1 if the string expr matches the regular expression specified by the pattern_pat, 0 otherwise. Ifexpr_ or_pat_ is NULL, the return value is NULL.
  REGEXP andRLIKE are synonyms for REGEXP_LIKE().
  For additional information about how matching occurs, see the description forREGEXP_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 pattern_pat, 0 if there is no match. Ifexpr_ or_pat_ is NULL, the return value is NULL. Character indexes begin at 1.
  REGEXP_INSTR() takes these optional arguments:
  • pos: The position in_expr_ 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.
  • returnoption: 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.
  • matchtype: 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 forREGEXP_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 pattern_pat, 0 otherwise. Ifexpr_ or_pat_ is NULL, the return value is NULL.
  The pattern can be an extended regular expression, the syntax for which is discussed inRegular 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 matchtype 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 matchtype, the rightmost one takes precedence.
    By default, regular expression operations use the character set and collation of the_expr_ and_pat_ 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 inSection 12.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 |  
+------------------------------------------------------------------+  

matchtype may be specified with the c or i 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 if matchtype contains the i character.
Note
MySQL uses C escape syntax in strings (for example,\n to represent the newline character). If you want your_expr_ or_pat_ 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 pattern_pat_ with the replacement string_repl, and returns the resulting string. If expr,pat, orrepl_ isNULL, the return value isNULL.
  REGEXP_REPLACE() takes these optional arguments:
  • pos: The position in_expr_ 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”).
  • matchtype: 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 forREGEXP_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 pattern_pat, NULL if there is no match. If expr orpat_ is NULL, the return value is NULL.
  REGEXP_SUBSTR() takes these optional arguments:
  • pos: The position in_expr_ 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.
  • matchtype: 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 forREGEXP_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                                          |  
+----------------------------------------------+