Description\n-----------\n\nPCRE Versions\n\n| PCRE Version                         | Introduced                                                                                                                                                                                                                                                                                                                               | Maturity |\n| ------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------- |\n| PCRE2 10.34 | MariaDB 10.5.1                                                                                                                                                                                                                               | Stable   |\n| PCRE 8.43                            | MariaDB 10.1.39                                                                                                                                                                                                                             | Stable   |\n| PCRE 8.42                            | MariaDB 10.2.15, MariaDB 10.1.33, MariaDB 10.0.35 | Stable   |\n| PCRE 8.41                            | MariaDB 10.2.8, MariaDB 10.1.26, MariaDB 10.0.32   | Stable   |\n| PCRE 8.40                            | MariaDB 10.2.5, MariaDB 10.1.22, MariaDB 10.0.30   | Stable   |\n| PCRE 8.39                            | MariaDB 10.1.15, MariaDB 10.0.26                                                                                                               | Stable   |\n| PCRE 8.38                            | MariaDB 10.1.10, MariaDB 10.0.23                                                                                                               | Stable   |\n| PCRE 8.37                            | MariaDB 10.1.5, MariaDB 10.0.18                                                                                                                 | Stable   |\n| PCRE 8.36                            | MariaDB 10.1.2, MariaDB 10.0.15                                                                                                                 | Stable   |\n| PCRE 8.35                            | MariaDB 10.1.0, MariaDB 10.0.12                                                                                                                 | Stable   |\n| PCRE 8.34                            | MariaDB 10.0.8                                                                                                                                                                                                                               | Stable   |\n\nPCRE Enhancements\n\nMariaDB uses the PCRE library, which significantly improves the power of the REGEXP/RLIKE operator.\n\nThe switch to PCRE added a number of features, including recursive patterns, named capture, look-ahead and look-behind assertions, non-capturing groups, non-greedy quantifiers, Unicode character properties, extended syntax for characters and character classes, multi-line matching, and many other.\n\nThese functions work with regular expressions: REGEXP_REPLACE(), REGEXP_INSTR(), and REGEXP_SUBSTR().\n\nAlso, REGEXP/RLIKE, and the new functions, work correctly with all multi-byte character sets supported by MariaDB, including East-Asian character sets (big5, gb2313, gbk, eucjp, eucjpms, cp932, ujis, euckr), and Unicode character sets (utf8, utf8mb4, ucs2, utf16, utf16le, utf32).\n\nNew Regular Expression Functions\n\n REGEXP_REPLACE(subject, pattern, replace) - Replaces all occurrences of a pattern.\n REGEXP_INSTR(subject, pattern) - Position of the first appearance of a regex.\n REGEXP_SUBSTR(subject,pattern) - Returns the matching part of a string.\n\nSee the individual articles for more details and examples.\n\nPCRE Syntax\n\nIn most cases PCRE is backward compatible with the old POSIX 1003.2 compliant regexp library (see Regular Expressions Overview), so you won't need to change your applications that use SQL queries with the REGEXP/RLIKE predicate.\n\nThis section briefly describes the most important extended PCRE features. For more details, please refer to the documentation on the PCRE site, or to the documentation which is bundled in the /pcre/doc/html/ directory of a MariaDB sources distribution. The pages pcresyntax.html and pcrepattern.html should be a good start. Regular-Expressions.Info is another good resource to learn about PCRE and regular expressions generally.\n\nSpecial Characters\n\nPCRE supports the following escape sequences to match special characters:\n\n| Sequence  | Description                                 |\n| --------- | ------------------------------------------- |\n| \\a        | 0x07 (BEL)                                  |\n| \\cx       | "control-x", where x is any ASCII character |\n| \\e        | 0x1B (escape)                               |\n| \\f        | 0x0C (form feed)                            |\n|           | 0x0A (newline)                              |\n|           | 0x0D (carriage return)                      |\n|           | 0x09 (TAB)                                  |\n| \\ddd      | character with octal code ddd               |\n| \\xhh      | character with hex code hh                  |\n| \\x{hhh..} | character with hex code hhh..               |\n\nNote, the backslash characters (here, and in all examples in the sections below) must be escaped with another backslash, unless you're using the SQL_MODE NO_BACKSLASH_ESCAPES.\n\nThis example tests if a character has hex code 0x61:\n\n``sql\nSELECT 'a' RLIKE '\\\\x{61}';\n-> 1\n`\n\nCharacter Classes\n\nPCRE supports the standard POSIX character classes such as alnum, alpha, blank, cntrl, digit, graph, lower, print, punct, space, upper, xdigit, with the following additional classes:\n\n| Class | Description                                                |\n| ----- | ---------------------------------------------------------- |\n| ascii | any ASCII character (0x00..0x7F)                           |\n| word  | any "word" character (a letter, a digit, or an underscore) |\n\nThis example checks if the string consists of ASCII characters only:\n\n`\nSELECT 'abc' RLIKE '^[[:ascii:]]+$';\n-> 1\n`\n\nGeneric Character Types\n\nGeneric character types complement the POSIX character classes and serve to simplify writing patterns:\n\n| Class | Description                                                |\n| ----- | ---------------------------------------------------------- |\n| \\d    | a decimal digit (same as \\[:digit:])                       |\n| \\D    | a character that is not a decimal digit                    |\n| \\h    | a horizontal white space character                         |\n| \\H    | a character that is not a horizontal white space character |\n|       | a character that is not a new line                         |\n|       | a newline sequence                                         |\n| \\s    | a white space character                                    |\n| \\S    | a character that is not a white space character            |\n| \\v    | a vertical white space character                           |\n| \\V    | a character that is not a vertical white space character   |\n| \\w    | a "word" character (same as \\[:word:])                     |\n| \\W    | a "non-word" character                                     |\n\nThis example checks if the string consists of "word" characters only:\n\n`sql\nSELECT 'abc' RLIKE '^\\\\w+$';\n-> 1\n`\n\nUnicode Character Properties\n\n\\p{xx} is a character with the xx property, and \\P{xx} is a character without the xx property.\n\nThe property names represented by xx above are limited to the Unicode script names, the general category properties, and "Any", which matches any character (including newline). Those that are not part of an identified script are lumped together as "Common".\n\nGeneral Category Properties For \\p and \\P\n\n| Property | Description           |\n| -------- | --------------------- |\n| C        | Other                 |\n| Cc       | Control               |\n| Cf       | Format                |\n| Cn       | Unassigned            |\n| Co       | Private use           |\n| Cs       | Surrogate             |\n| L        | Letter                |\n| Ll       | Lower case letter     |\n| Lm       | Modifier letter       |\n| Lo       | Other letter          |\n| Lt       | Title case letter     |\n| Lu       | Upper case letter     |\n| L&       | Ll, Lu, or Lt         |\n| M        | Mark                  |\n| Mc       | Spacing mark          |\n| Me       | Enclosing mark        |\n| Mn       | Non-spacing mark      |\n| N        | Number                |\n| Nd       | Decimal number        |\n| Nl       | Letter number         |\n| No       | Other number          |\n| P        | Punctuation           |\n| Pc       | Connector punctuation |\n| Pd       | Dash punctuation      |\n| Pe       | Close punctuation     |\n| Pf       | Final punctuation     |\n| Pi       | Initial punctuation   |\n| Po       | Other punctuation     |\n| Ps       | Open punctuation      |\n| S        | Symbol                |\n| Sc       | Currency symbol       |\n| Sk       | Modifier symbol       |\n| Sm       | Mathematical symbol   |\n| So       | Other symbol          |\n| Z        | Separator             |\n| Zl       | Line separator        |\n| Zp       | Paragraph separator   |\n| Zs       | Space separator       |\n\nThis example checks if the string consists only of characters with property N (number):\n\n`sql\nSELECT '1¼①' RLIKE '^\\\\p{N}+$';\n-> 1\n`\n\nSpecial Category Properties For \\p and \\P\n\n| Property | Description                                                       |\n| -------- | ----------------------------------------------------------------- |\n| Xan      | Alphanumeric: union of properties L and N                         |\n| Xps      | POSIX space: property Z or tab, NL, VT, FF, CR                    |\n| Xsp      | Perl space: property Z or tab, NL, FF, CR                         |\n| Xuc      | A character than can be represented by a Universal Character Name |\n| Xwd      | Perl word: property Xan or underscore                             |\n\nThe property Xuc matches any character that can be represented by a Universal Character Name (in C++ and other programming languages). These include $, @, \\\\\\, and all characters with Unicode code points greater than U+00A0, excluding the surrogates U+D800..U+DFFF.\n\nScript Names For \\p and \\P\n\nArabic, Armenian, Avestan, Balinese, Bamum, Batak, Bengali, Bopomofo, Brahmi, Braille, Buginese, Buhid, Canadian_Aboriginal, Carian, Chakma, Cham, Cherokee, Common, Coptic, Cuneiform, Cypriot, Cyrillic, Deseret, Devanagari, Egyptian_Hieroglyphs, Ethiopic, Georgian, Glagolitic, Gothic, Greek, Gujarati, Gurmukhi, Han, Hangul, Hanunoo, Hebrew, Hiragana, Imperial_Aramaic, Inherited, Inscriptional_Pahlavi, Inscriptional_Parthian, Javanese, Kaithi, Kannada, Katakana, Kayah_Li, Kharoshthi, Khmer, Lao, Latin, Lepcha, Limbu, Linear_B, Lisu, Lycian, Lydian, Malayalam, Mandaic, Meetei_Mayek, Meroitic_Cursive, Meroitic_Hieroglyphs, Miao, Mongolian, Myanmar, New_Tai_Lue, Nko, Ogham, Old_Italic, Old_Persian, Old_South_Arabian, Old_Turkic, Ol_Chiki, Oriya, Osmanya, Phags_Pa, Phoenician, Rejang, Runic, Samaritan, Saurashtra, Sharada, Shavian, Sinhala, Sora_Sompeng, Sundanese, Syloti_Nagri, Syriac, Tagalog, Tagbanwa, Tai_Le, Tai_Tham, Tai_Viet, Takri, Tamil, Telugu, Thaana, Thai, Tibetan, Tifinagh, Ugaritic, Vai, Yi.\n\nThis example checks if the string consists only of Greek characters:\n\n``sql\nSELECT 'ΣΦΩ' RLIKE '^\\\\p{Greek}+$';\n-> 1\n`\n\nExtended Unicode Grapheme Sequence\n\nThe \\X escape sequence matches a character sequence that makes an "extended grapheme cluster", i.e. a composite character that consists of multiple Unicode code points.\n\nOne of the examples of a composite character can be a letter followed by non-spacing accent marks. This example demonstrates that U+0045 LATIN CAPITAL LETTER E followed by U+0302 COMBINING CIRCUMFLEX ACCENT followed by U+0323 COMBINING DOT BELOW together form an extended grapheme cluster:\n\n`sql\nSELECT _ucs2 0x004503020323 RLIKE '^\\\\X$';\n-> 1\n`\n\nSee the PCRE documentation for the other types of extended grapheme clusters.\n\nSimple Assertions\n\nAn assertion specifies a certain condition that must match at a particular point, but without consuming characters from the subject string. In addition to the standard POSIX simple assertions ^ (that matches at the beginning of a line) and $ (that matches at the end of a line), PCRE supports a number of other assertions:\n\n| Assertion | Description                                                                                |\n| --------- | ------------------------------------------------------------------------------------------ |\n| \\b        | matches at a word boundary                                                                 |\n| \\B        | matches when not at a word boundary                                                        |\n| \\A        | matches at the start of the subject                                                        |\n| \\Z        | matches at the end of the subject, also matches before a newline at the end of the subject |\n| \\z        | matches only at the end of the subject                                                     |\n| \\G        | matches at the first matching position in the subject                                      |\n\nThis example cuts a word that consists only of 3 characters from a string:\n\n`sql\nSELECT REGEXP_SUBSTR('---abcd---xyz---', '\\\\b\\\\w{3}\\\\b');\n-> xyz\n`\n\nNotice that the two \\b assertions checked the word boundaries but did not get into the matching pattern.\n\nThe \\b assertions work well in the beginning and the end of the subject string:\n\n`sql\nSELECT REGEXP_SUBSTR('xyz', '\\\\b\\\\w{3}\\\\b');\n-> xyz\n`\n\nBy default, the ^ and $ assertions have the same meaning with \\A, \\Z, and \\z. However, the meanings of ^ and $ can change in multiline mode (see below). By contrast, the meanings of \\A, \\Z, and \\z are always the same; they are independent of the multiline mode.\n\nOption Setting\n\nA number of options that control the default match behavior can be changed within the pattern by a sequence of option letters enclosed between (? and ).\n\n| Option  | Description                                                              |\n| ------- | ------------------------------------------------------------------------ |\n| (?i)    | case insensitive match                                                   |\n| (?m)    | multiline mode                                                           |\n| (?s)    | dotall mode (dot matches newline characters)                             |\n| (?x)    | extended (ignore white space)                                            |\n| (?U)    | ungreedy (lazy) ma\n\n[Truncated — full content at mariadb.com/docs]