Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.0Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 33.0Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 132.9Kb
Man Pages (Zip) - 189.5Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Regular Expressions

12.5.2 Regular Expressions

Table 12.9 Regular Expression Functions and Operators

Name Description
NOT REGEXP Negation of REGEXP
REGEXP Whether string matches regular expression
REGEXP_INSTR() Starting index of substring matching regular expression
REGEXP_LIKE() Whether string matches regular expression
REGEXP_REPLACE() Replace substrings matching regular expression
REGEXP_SUBSTR() Return substring matching regular expression
RLIKE Whether string matches regular expression

A regular expression is a powerful way of specifying a pattern for a complex search. This section discusses the functions and operators available for regular expression matching and illustrates, with examples, some of the special characters and constructs that can be used for regular expression operations. See also Section 3.3.4.7, “Pattern Matching”.

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. (Prior to MySQL 8.0.4, MySQL used Henry Spencer's implementation of regular expressions, which operates in byte-wise fashion and is not multibyte safe. For information about ways in which applications that use regular expressions may be affected by the implementation change, see Regular Expression Compatibility Considerations.)

Regular Expression Functions and Operators

  • 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. If expr or pat is NULL, the return value is NULL.

    REGEXP and RLIKE are synonyms for REGEXP_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 |
    +---------------------+
    mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
    +----------------+-----------------------+
    | 'a' REGEXP 'A' | 'a' REGEXP BINARY 'A' |
    +----------------+-----------------------+
    |              1 |                     0 |
    +----------------+-----------------------+
  • 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. If expr 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.

    • 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 for REGEXP_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 pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.

    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 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 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 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 match_type contains the i character.

    Note

    Because 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 expr and pat arguments.

    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('a', 'A'), REGEXP_LIKE('a', BINARY 'A');
    +-----------------------+------------------------------+
    | REGEXP_LIKE('a', 'A') | REGEXP_LIKE('a', BINARY 'A') |
    +-----------------------+------------------------------+
    |                     1 |                            0 |
    +-----------------------+------------------------------+
    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, or repl is NULL, the return value is NULL.

    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).

    • match_type: A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().

    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 pattern pat, NULL if there is no match. If expr or pat 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.

    • match_type: A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().

    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                                          |
    +----------------------------------------------+

Regular Expression Syntax

A regular expression describes a set of strings. The simplest regular expression is one that has no special characters in it. For example, the regular expression hello matches hello and nothing else.

Nontrivial regular expressions use certain special constructs so that they can match more than one string. For example, the regular expression hello|world contains the | alternation operator and matches either the hello or world.

As a more complex example, the regular expression B[an]*s matches any of the strings Bananas, Baaaaas, Bs, and any other string starting with a B, ending with an s, and containing any number of a or n characters in between.

The following list covers some of the basic special characters and constructs that can be used in regular expressions. For information about the full regular expression syntax supported by the ICU library used to implement regular expression support, visit the International Components for Unicode website.

  • ^

    Match the beginning of a string.

    mysql> SELECT REGEXP_LIKE('fo\nfo', '^fo$');                   -> 0
    mysql> SELECT REGEXP_LIKE('fofo', '^fo');                      -> 1
  • $

    Match the end of a string.

    mysql> SELECT REGEXP_LIKE('fo\no', '^fo\no$');                 -> 1
    mysql> SELECT REGEXP_LIKE('fo\no', '^fo$');                    -> 0
  • .

    Match any character (including carriage return and newline, although to match these in the middle of a string, the m (multiple line) match-control character or the (?m) within-pattern modifier must be given).

    mysql> SELECT REGEXP_LIKE('fofo', '^f.*$');                    -> 1
    mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$');                -> 0
    mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$', 'm');           -> 1
    mysql> SELECT REGEXP_LIKE('fo\r\nfo', '(?m)^f.*$');           -> 1
  • a*

    Match any sequence of zero or more a characters.

    mysql> SELECT REGEXP_LIKE('Ban', '^Ba*n');                     -> 1
    mysql> SELECT REGEXP_LIKE('Baaan', '^Ba*n');                   -> 1
    mysql> SELECT REGEXP_LIKE('Bn', '^Ba*n');                      -> 1
  • a+

    Match any sequence of one or more a characters.

    mysql> SELECT REGEXP_LIKE('Ban', '^Ba+n');                     -> 1
    mysql> SELECT REGEXP_LIKE('Bn', '^Ba+n');                      -> 0
  • a?

    Match either zero or one a character.

    mysql> SELECT REGEXP_LIKE('Bn', '^Ba?n');                      -> 1
    mysql> SELECT REGEXP_LIKE('Ban', '^Ba?n');                     -> 1
    mysql> SELECT REGEXP_LIKE('Baan', '^Ba?n');                    -> 0
  • de|abc

    Alternation; match either of the sequences de or abc.

    mysql> SELECT REGEXP_LIKE('pi', 'pi|apa');                     -> 1
    mysql> SELECT REGEXP_LIKE('axe', 'pi|apa');                    -> 0
    mysql> SELECT REGEXP_LIKE('apa', 'pi|apa');                    -> 1
    mysql> SELECT REGEXP_LIKE('apa', '^(pi|apa)$');                -> 1
    mysql> SELECT REGEXP_LIKE('pi', '^(pi|apa)$');                 -> 1
    mysql> SELECT REGEXP_LIKE('pix', '^(pi|apa)$');                -> 0
  • (abc)*

    Match zero or more instances of the sequence abc.

    mysql> SELECT REGEXP_LIKE('pi', '^(pi)*$');                    -> 1
    mysql> SELECT REGEXP_LIKE('pip', '^(pi)*$');                   -> 0
    mysql> SELECT REGEXP_LIKE('pipi', '^(pi)*$');                  -> 1
  • {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 atom (or piece) 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}.

    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 REGEXP_LIKE('abcde', 'a[bcd]{2}e');              -> 0
    mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{3}e');              -> 1
    mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{1,10}e');           -> 1
  • [a-dX], [^a-dX]

    Matches any character that is (or is not, if ^ is used) either a, b, c, d or X. A - character between two other characters forms a range that matches all characters from the first character to the second. For example, [0-9] matches any decimal digit. To include a literal ] character, it must immediately follow the opening bracket [. To include a literal - character, it must be written first or last. Any character that does not have a defined special meaning inside a [] pair matches only itself.

    mysql> SELECT REGEXP_LIKE('aXbc', '[a-dXYZ]');                 -> 1
    mysql> SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]$');               -> 0
    mysql> SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]+$');              -> 1
    mysql> SELECT REGEXP_LIKE('aXbc', '^[^a-dXYZ]+$');             -> 0
    mysql> SELECT REGEXP_LIKE('gheis', '^[^a-dXYZ]+$');            -> 1
    mysql> SELECT REGEXP_LIKE('gheisa', '^[^a-dXYZ]+$');           -> 0
  • [=character_class=]

    Within a bracket expression (written using [ and ]), [=character_class=] represents an equivalence class. It matches all characters with the same collation value, including itself. For example, if o and (+) are the members of an equivalence class, [[=o=]], [[=(+)=]], and [o(+)] are all synonymous. An equivalence class may not be used as an endpoint of a range.

  • [:character_class:]

    Within a bracket expression (written using [ and ]), [:character_class:] represents a character class that matches all characters belonging to that class. The following table lists the standard class names. These names stand for the character classes defined in the ctype(3) manual page. A particular locale may provide other class names. A character class may not be used as an endpoint of a range.

    Character Class Name Meaning
    alnum Alphanumeric characters
    alpha Alphabetic characters
    blank Whitespace characters
    cntrl Control characters
    digit Digit characters
    graph Graphic characters
    lower Lowercase alphabetic characters
    print Graphic or space characters
    punct Punctuation characters
    space Space, tab, newline, and carriage return
    upper Uppercase alphabetic characters
    xdigit Hexadecimal digit characters
    mysql> SELECT REGEXP_LIKE('justalnums', '[[:alnum:]]+');       -> 1
    mysql> SELECT REGEXP_LIKE('!!', '[[:alnum:]]+');               -> 0

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 regular expression library interprets the other. For example, to match the string 1+2 that contains the special + character, only the last of the following regular expressions is the correct one:

mysql> SELECT REGEXP_LIKE('1+2', '1+2');                       -> 0
mysql> SELECT REGEXP_LIKE('1+2', '1\+2');                      -> 0
mysql> SELECT REGEXP_LIKE('1+2', '1\\+2');                     -> 1

Regular Expression Resource Control

REGEXP_LIKE() and similar functions use resources that can be controlled by setting system variables:

  • The match engine uses memory for its internal stack. To control the maximum available memory for the stack in bytes, set the regexp_stack_limit system variable.

  • The match engine operates in steps. To control the maximum number of steps performed by the engine (and thus indirectly the execution time), set the regexp_time_limit system variable. Because this limit is expressed as number of steps, it affects execution time only indirectly. Typically, it is on the order of milliseconds.

Regular Expression Compatibility Considerations

Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations, rather than International Components for Unicode (ICU). The following discussion describes differences between the Spencer and ICU libraries that may affect applications:

  • With the Spencer library, the REGEXP and RLIKE operators work in byte-wise fashion, so they are not multibyte safe and may produce unexpected results with multibyte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

    ICU has full Unicode support and is multibyte safe. Its regular expression functions treat all strings as as UTF-16. You should keep in mind that positional indexes are based on 16-bit chunks and not on code points. This means that, when passed to such functions, characters using more than one chunk may produce unanticipated results, such as those shown here:

    mysql> SELECT REGEXP_INSTR('🍣🍣b', 'b');
    +--------------------------+
    | REGEXP_INSTR('??b', 'b') |
    +--------------------------+
    |                        5 |
    +--------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT REGEXP_INSTR('🍣🍣bxxx', 'b', 4);
    +--------------------------------+
    | REGEXP_INSTR('??bxxx', 'b', 4) |
    +--------------------------------+
    |                              5 |
    +--------------------------------+
    1 row in set (0.00 sec)

    Characters within the Unicode Basic Multilingual Plane, which includes characters used by most modern languages, are safe in this regard:

    mysql> SELECT REGEXP_INSTR('бжb', 'b');
    +----------------------------+
    | REGEXP_INSTR('бжb', 'b')   |
    +----------------------------+
    |                          3 |
    +----------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT REGEXP_INSTR('עבb', 'b');
    +----------------------------+
    | REGEXP_INSTR('עבb', 'b')   |
    +----------------------------+
    |                          3 |
    +----------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT REGEXP_INSTR('µå周çб', '周');
    +------------------------------------+
    | REGEXP_INSTR('µå周çб', '周')       |
    +------------------------------------+
    |                                  3 |
    +------------------------------------+
    1 row in set (0.00 sec)

    Emoji, such as the sushi character 🍣 (U+1F363) used in the first two examples, are not included in the Basic Multilingual Plane, but rather in Unicode's Supplementary Multilingual Plane. Another issue can arise with emoji and other 4-byte characters when REGEXP_SUBSTR() or a similar function begins searching in the middle of a character. Each of the two statements in the following example starts from the second 2-byte position in the first argument. The first statement works on a string consisting solely of 2-byte (BMP) characters. The second statement contains 4-byte characters which are incorrectly interpreted in the result because the first two bytes are stripped off and so the remainder of the character data is misaligned.

    mysql> SELECT REGEXP_SUBSTR('周周周周', '.*', 2);
    +----------------------------------------+
    | REGEXP_SUBSTR('周周周周', '.*', 2)     |
    +----------------------------------------+
    | 周周周                                 |
    +----------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT REGEXP_SUBSTR('🍣🍣🍣🍣', '.*', 2);
    +--------------------------------+
    | REGEXP_SUBSTR('????', '.*', 2) |
    +--------------------------------+
    | ?㳟揘㳟揘㳟揘                  |
    +--------------------------------+
    1 row in set (0.00 sec)
  • For the . operator, the Spencer library matches line-terminator characters (carriage return, newline) anywhere in string expressions, including in the middle. To match line terminator characters in the middle of strings with ICU, specify the m match-control character.

  • The Spencer library supports word-beginning and word-end boundary markers ([[:<:]] and [[:>:]] notation). ICU does not.

  • The Spencer library supports collating element bracket expressions ([.characters.] notation). ICU does not.

  • For repetition counts ({n} and {m,n} notation), the Spencer library has a maximum of 255. ICU has no such limit, although the maximum number of match engine steps can be limited by setting the regexp_time_limit system variable.

  • ICU interprets parentheses as metacharacters. To specify a literal open parenthesis ( in a regular expression, it must be escaped:

    mysql> SELECT REGEXP_LIKE('(', '(');
    ERROR 3692 (HY000): Mismatched parenthesis in regular expression.
    mysql> SELECT REGEXP_LIKE('(', '\\(');
    +-------------------------+
    | REGEXP_LIKE('(', '\\(') |
    +-------------------------+
    |                       1 |
    +-------------------------+

User Comments
  Posted by D. Meanea on May 30, 2003
If you are searching for literal parentheses, you have to enclose each parenthesis in brackets; otherwise, mySQL thinks they're part of the regular expression syntax. For instance:

WHERE phone REGEXP '(435)';

would return any phone numbers that have the sequence 435 in any part of the string, such as "1(801)555-4351". However:

WHERE phone REGEXP '[(]435[)]';

would return only phone numbers with (435), such as "1(435)555-5555".
  Posted by Marcello Alves on June 5, 2003
It's far beyond the scope of this documentation to dwell on all the gory details of regular expressions. Should you have any doubts, please refer to a good book on the subject like "Mastering Regular Expressions" (http://www.oreilly.com/catalog/regex/). References online include http://sitescooper.org/tao_regexps.html and http://www.regexlib.com/
  Posted by a j stiles on November 3, 2003
The regular expression support in MySQL seems to be based on traditional-style regex (like ereg() in PHP), not the more sophisticated regular expression matching found in Perl or PHP's preg_match(). And in case the above doesn't make it clear (being mostly SELECT statements using the function directly to return a 1 or 0), you typically would use the REGEXP function in a WHERE clause like this:

SELECT * FROM foo WHERE bar REGEXP "baz"

To match a "special" character such as $, you need to prefix it with the backslash \ character. So \$ matches an actual dollar sign. However, in almost any programming language that claims a "C-like" syntax, that backslash is likely to get picked up on as a special character. So you may need to use an extra backslash. Also, at least in Perl and PHP, the $ is a special character itself, because it indicates that what follows is a variable name - so it will need a backslash too.

In Perl or PHP, you probably will write something like this to match on a line starting with a $ sign:

$query = "SELECT * FROM `foo` WHERE `bar` REGEXP \"^\\\$\""

I'll explain the special characters in that and what they mean:
backslash, speech mark = a literal speech mark
HAT sign = beginning of line
two backslashes = a literal backslash
backslash, dollar = a literal dollar sign
backslash, speech mark = a literal speech mark

Now if you print $query, it will have the value
SELECT * FROM `foo` WHERE `bar` REGEXP "^\$"
which is what you really want, and how you would type it into the mysql command line. Remember also that PHPMyAdmin expects you to put a backslash before a backslash or apostrophe. So in PHPMyAdmin you would enter
SELECT * FROM `foo` WHERE `bar` REGEXP "^\\$"

I guess if you only want to use . and .* regular expressions, you may as well stick to using LIKE with the _ and % wildcards, as that is probably a bit faster. Finally, when using regular expressions in *any* language you need to watch out, because it is very easy to write ones that will always match, and almost as easy to write ones that will never match anything. So do check!
  Posted by Vaz Aranni on June 30, 2005
Regexp's are pretty complicated. If you need anything more complicated than what's shown above, a good site to learn how to use them is http://www.regular-expressions.info. The sites listed in the second comment are probably a good idea too.

Also, don't get in the habit of using character classes to escape metacharacters (like using [(] to match a literal parenthese). The open parentheses '(' and ')' have no special meaning inside a character class, but others like $ retain their meaning. Using [$] won't match the character '$', it'll still match the beginning of the string.

So escape them properly with backslashes.

So what if your PHP code has something that looks like...

mysql_query("select * from a where Name rlike '(^|//////|)example'")

...just to match a string that starts with "example" or contains the string "|example". It looks terrible and it works.
  Posted by Tony Boyd on October 12, 2005
For those of you struggling to escape special characters with long sequences of backslashes (see Vaz's post), I have to ask: why bother? Why not just use the dot-character syntax mentioned on this very page? For example, I needed to find all the ID attributes in some HTML. I tried escaping single and double-quotes for about 30 seconds, then I just switched to this:

SELECT * FROM site WHERE html REGEXP "id=[[.apostrophe.][.quotation-mark.]]archives[[.apostrophe.][.quotation-mark.]]";

Ta da. No escaping issues.
  Posted by Guido Dieterich on December 20, 2005
This sql statements:

SELECT 'WORD' REGEXP '[[:upper:]]{4}'; # => 1;
SELECT 'WORD' REGEXP '[[:lower:]]{4}'; # => 0
work right only when collate is _cs and NOT _ci (case insensitive)

created tables eg. the collate 'latin1_swedish_ci' have to be changed, if you want to use case sensitive REGEXPs like [[:upper:]] or [[:lower:]]!
ALTER TABLE <name> CONVERT TO CHARACTER SET latin1 COLLATE latin_general_cs

I set in my.cnf now:
[mysqld]
default-collation= latin1_general_cs

#default was latin1_swedish_ci
  Posted by Koy Kragh on March 4, 2006
The above post by Guido Dieterich (about collation and case sensitivity) is a good point. However, there is a way to match in a case-sensitive manner without having to change the collation of your existing tables: use the "BINARY" keyword.

Here's an extended example (based on the one previously posted):

SELECT
('WORD' REGEXP '[[:upper:]]{4}') AS `upper_match`, # this will be a 1
('WORD' REGEXP '[[:lower:]]{4}') AS `lower_match`, # this will be a 1 on an "*_ci" collation
# -BINARY- matches below
(BINARY 'WORD' REGEXP '[[:upper:]]{4}') AS `bin_upper_match`, # this will be a 1
(BINARY 'WORD' REGEXP '[[:lower:]]{4}') AS `bin_lower_match` # this will be a 0 even on an "*_ci" collation
  Posted by Dennis K on November 8, 2006
The query "SELECT * FROM table WHERE text REGEXP 'UPPER'" on a *.ci (e.g. latin1_general_ci) table will find any case insensetive words, even words like "upper" or "uPpOr", or "UpPOr", etc...

To avoid this use one of the following Methods:

SELECT * FROM table WHERE text COLLATE latin1_general_cs REGEXP '...'

OR

SELECT * FROM table WHERE CAST(x AS BINARY) REGEXP '...'

  Posted by richard versloot on May 9, 2007
information on boosting performance of regexp matching would be very welcome (for example: wich index type)
This information can't be find on the dev.mysql.com site.
  Posted by lvaro G. Vicario on October 10, 2007
"To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters."

If you are coding in PHP and you need to match a literal backslash you can easily end up with an unmaintainable mess of \\\\\\\\'s.

Try to keep it clean applying the appropriate escaping functions:


<?php

$match 
'^' preg_quote('one\\two') . '$';
$sql "SELECT * FROM my_table " .
    
"WHERE my_field REGEXP '" mysql_real_escape_string($match) . "'";

?>


There doesn't seem to be a native quote funcion in MySQL but PHP's preg_quote() apparently works.
  Posted by Carl Longnecker on December 30, 2007
richard versloot-

regular expressions do not utilize indexes. the only way to improve their performance is to write a more efficient expression.
  Posted by Donoiu Cristian on July 3, 2008
Some of the next lines may be true:

-place the fixed/bigger part first or "fail fast", for ex: instead of (e|abcd) use (abcd|e)
-extract similitudes, ex: instead of (abcd|abef) use ab(cd|ef)
-prefere LIKE with % and _ , or string functions if the match is simple
-try to create less cycles(regex use a lot backtracking)!!!

Hope it helps!
  Posted by Alan Ng on November 7, 2009
I can't believe I'm the first to post this solution, since the pain of MySQL's REGEXP not working with multibyte character sets has been expressed all over the Web for years, I see.

Here's my simple workaround, for a database, server, and current connection that are entirely in UTF-8. Of course this only helps the majority of us developers who are in fact dealing with stored data that could just as well have be expressed in latin1:

SELECT * FROM YourTables WHERE (CONVERT (TextField USING latin1)) REGEXP CONVERT ('YourUTF8RegExp' USING latin1))

  Posted by Miklos Kokenyesi on November 30, 2009
Maybe this will be useful for others.
While creating a search function with syntax-highlighting for content which include bulletin board codes ([code], [ul], [li], etc.) I was stuck for a few hours about how to exclude the contents of the BBC tags. For example, if someone search for "ode", the [code] tags won't needed in the result list, not to mention that the syntax highlighting destroyed the html code as well :)

I was only able to do it this way ($search is the search string, passed from php):

SELECT * FROM table
WHERE LOWER(content) LIKE '%$search%'
AND content REGEXP '[\]].[^\[]*$search'

I wanted my $search to be found only after "]", and only if there is no "[" between them. Other characters are allowed.
  Posted by Jim W on July 13, 2010
Since character code escape sequences aren't supported, here's a handy regexp for finding any rows with characters outside of the ASCII range:

SELECT * FROM my_table
WHERE the_column REGEXP '[^[.NUL.]-[.DEL.]]'

Alternately, if you want to exclude control characters as well:

SELECT * FROM my_table
WHERE the_column REGEXP '[^ -~]'
  Posted by ehab heikal on September 21, 2010
For those times when you need a fast reference for regex you can download and print or view this pdf http://www.elmotaheda.com/website-design-development/regex_cheat_cheat .

The sheet is color coded and is very easy to read.
  Posted by Kasey Speakman on November 10, 2010
It's quite unfortunate that their REGEX interpreter doesn't support logical NOT expressions. Ordinarily I would write something like this to match all numbers except 11:

(?!^11$)^\d+$

But with their implementation, I have to make a "do not match" field as well as a "match" field to make sure my conditions are met. :/
  Posted by Andrey Klyuchnikov on March 1, 2011
re: Kasey Speakman

^([0-9]|[02-9][02-9]|[0-9][02-9]|[02-9][0-9]|[0-9]{3,})$

:)
  Posted by Naved Shah on May 13, 2011
For LIKE Results

SELECT * FROM REG where seg_3 REGEXP 'SHA|S11|A11|SH4|SH4H|H4H|5HA|5HAH|HAH'

SNUSHAH

  Posted by Jon Spriggs on November 24, 2011
I struggled with the [:character_type:] element of the regexp. I had a problem where users were entering artist names with various degrees of spacing in the name... for example "Loudog" and "Lou Dog".

To get around this, I changed the string being searched for, in code, to: "[:space:]*L[:space:]*o[:space:]*u[:space:]*D[:space:]*o[:space:]*g[:space:]*" but this wasn't working.

Eventually someone spotted that I was essentially searching for zero-or-more instances of the characters :, s, p, a, c or e. This search should instead have been: "[[:space:]]*L[[:space:]]*o[[:space:]]*u[[:space:]]*D[[:space:]]*o[[:space:]]*g[[:space:]]*"

It's not clear in the examples above that this is what you should be searching for. This is roughly equivelent to "/\s+L\s+o\s+u\s+D\s+o\s+g\s+/i"

  Posted by Kasey Speakman on December 8, 2011
@Andrey Klyuchnikov

Using ORed decompositions is fine for my simple example, but in actual data, the length of the decompositions may be well beyond the point where they would be worth doing over just using two fields... or better yet if MySQL would implement the negation operator ?!.

Also, decompositions are much less clear in what they are doing. It's pretty obvious what the negation operator is doing. When I have to revisit a regex 2 years from now to add something to it, I'm going to want to smack someone that used a decomp.
  Posted by Eric Kent on December 23, 2011
If you need an IsNumeric or IsInt function, you can use:
return sValue regexp '^-?[0-9]+[.]?[0-9]*$|^-?[.][0-9]+$';
or
return sValue regexp '^-?[0-9]+$';
where sValue is a char argument.
  Posted by Naftali Zakharov on May 2, 2013
Ehab Heikal, thanks for your link to the regex cheat sheet. Only, the content is no longer there. I am aware of some other regex cheat sheets that seem very popular and I am sharing a short list here.

1) A pretty neutral cheat sheet for regexes.
The download is for free.
http://www.cheatography.com/davechild/cheat-sheets/regular-expressions/
and there is a nicer-looking one for a symbolic fee, here:
http://www.addedbytes.com/cheat-sheets/regular-expressions-cheat-sheet/

2) While we are at it, here's a MySQL cheat sheet by the same author:
http://www.addedbytes.com/download/mysql-cheat-sheet-v1/pdf/

3) Another regex cheat sheet:
http://www.regular-expressions.info/reference.html

Hope this is helpful.
Sign Up Login You must be logged in to post a comment.