MariaDB RLIKE Function
The MariaDB RLIKE function is used to perform regular expression matching. It returns 1 if the expression matches the regular expression specified by pattern, else returns 0. If expression or pattern is NULL, the return value will be NULL.
The RLIKE function performs a case-insensitive match, except when used with binary strings.
The RLIKE function is a synonym for the REGEXP function.
Syntax
expression RLIKE pattern
Parameters
expression |
Required. Specify a character expression such as a column or field. |
pattern |
Required. Specify the regular expression matching information. See the table below for pattern syntax. |
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 |
Example: Match on more than one alternative
Consider a table called Employee. The below query is used to fetch all records from this table where Name contains either "Ja", "Je" or "Ji". To specify more than one alternative for the second character of the pattern, | is used.
SELECT * FROM Employee WHERE Name RLIKE 'J(a|e|i)';
Example: Match on Beginning
Consider a table called Employee. The below query is used to fetch all records from this table where Name starts with "J". Here, ^ is used to specify starting character of the pattern.
SELECT * FROM Employee WHERE Name RLIKE '^J';
Example: Match on End
Consider a table called Employee. The below query is used to fetch all records from this table where Name ends with "n". Here, $ is used to specify end character of the pattern.
SELECT * FROM Employee WHERE Name RLIKE 'n$';
Example: Matching List
Consider a table called Employee. The below query is used to fetch all records from this table where Name contains either "Ja", "Jb", "Jc" or "Jd". To specify the second character of the pattern, [ ] is used, which can be either "a", "b", "c" or "d" in this case.
SELECT * FROM Employee WHERE Name RLIKE 'J[a-d]';
Example: Non-Matching List
Consider a table called Employee. The below query is used to fetch all records from this table where Name does not contain either "Ja", "Jb", "Jc" or "Jd". The [ ^ ] pattern is used to specify characters which are not listed here.
SELECT * FROM Employee WHERE Name RLIKE 'J[^a-d]';
Example: Match Character Class
Consider a table called Employee. The below query is used to fetch all records from this table where Address contains a pattern which starts with 123 and then the remainder of the characters are alphanumeric characters.
SELECT * FROM Employee WHERE Address RLIKE '123[[:alnum:]]*';
Example: Escaping Characters
To escape special characters when using the RLIKE function, the special character should be preceded with two backslashes \\. Consider a table called Employee. The below query can be used to fetch all records of this table where Address contains a ( character.
SELECT * FROM Employee
WHERE Address RLIKE '\\(';
❮ MariaDB - REGEXP
