MySQL - REGEXP
A REGEXP, or Regular Expression is a sequence of characters that defines a search pattern. It is used to check whether a expression contains specified search pattern or not.
Apart from LIKE and NOT LIKE operators, MySQL supports another type of pattern matching operation based on REGEXP Functions and Operators.
MySQL REGEXP Functions and Operators
Following is the list of REGEXP Functions and Operators supported by MySQL:
| Name | Description |
|---|---|
| NOT REGEXP | Negation of REGEXP |
| NOT RLIKE | Negation of RLIKE |
| REGEXP | Used to check 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 | Used to check whether string matches regular expression |
Regular Expression Patterns
Following is the table for pattern syntax, which can be used with the REGEXP Functions and Operators.
Regular Expression Patterns
| Symbol | Description |
|---|---|
| ^ | Matches the beginning of a string. |
| $ | Matches the end of a string. |
| * | Matches zero or more occurrences. |
| + | Matches one or more occurrences. |
| ? | Matches zero or one occurrence. |
| . | Matches any character except NULL. |
| | | Used like an "OR" to specify more than one alternative. |
| [ ] | Matches any single character specified within []. |
| [^ ] | Matches any single character that is not specified within []. |
| - | Represents a range of characters. |
| ( ) | Used to group expressions as a subexpression. |
| {m} | Matches m times. |
| {m,} | Matches at least m times. |
| {m,n} | Matches at least m times, but no more than n times. |
| \n | n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n. |
| \d | Matches a digit character. |
| \D | Matches a nondigit character. |
| \w | Matches a word character. |
| \W | Matches a nonword character. |
| \s | Matches a whitespace character. |
| \S | matches a non-whitespace character. |
| *? | Matches the preceding pattern zero or more occurrences. |
| +? | Matches the preceding pattern one or more occurrences. |
| ?? | Matches the preceding pattern zero or one occurrence. |
| {n}? | Matches the preceding pattern n times. |
| {n,}? | Matches the preceding pattern at least n times. |
| {n,m}? | Matches the preceding pattern at least n times, but not more than m times. |
| [..] | Matches one collation element that can be more than one character. |
| [=character_class=] | Represents an equivalence class. It matches all characters with the same collation value, including itself. |
| [:character_class:] | Represents a character class that matches all characters belonging to that class. |
List of standard class name
| 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 |
| Graphic or space characters | |
| punct | Punctuation characters |
| space | Space, tab, newline, and carriage return |
| upper | Uppercase alphabetic characters |
| xdigit | Hexadecimal digit characters |
