Search code examples
mysqlregexcharacter-class

List of metacharacters for MySQL regexp square brackets

Strangely I can't seem to find anywhere a list of the characters that I can't safely use as literals within MySQL regular expression square brackets without escaping them or requiring the use of a [:character_class:] thing.

(Also the answer probably needs to be MySQL specific because MySQL regular expressions seem to be lacking compared those in Perl/PHP/Javascript etc).

Solution

  • Almost all metacharacters (including the dot ., the +, * and ? quantifiers, the end-of-string anchor $, etc.) have no special meaning in character classes, with a few notable exceptions:

    • closing bracket ], for obvious reasons
    • caret ^, which is used to negate the character class (eg: [^ab] matches any character but a and b).
    • hyphen -, which is used to denote a range (eg: [0-9] matches any digit)

    However, these can still be added without escaping if placed in strategic locations within the character class:

    • the closing bracket can be placed right after the opening bracket, eg: []a] matches ] or a.
    • the caret can be placed anywhere but after the opening bracket, eg: [a^] matches ^ or a
    • the hyphen can be placed right after the opening bracket or before the closing bracket, eg: [-a] and [a-] both match a and -.

    More information can be found in the man page on POSIX regex (thanks Tomalak Geret'kal!)