MariaDB REGEXP_REPLACE() Function
The MariaDB REGEXP_REPLACE() function replaces all occurrences in the string that match the regular expression specified by the pattern with the replacement string replace, and returns the resulting string. If the string or pattern is NULL, the return value will be NULL.
The replace string can have backreferences to the subexpressions in the form \N, where N is a number from 1 to 9.
By default, REGEXP_REPLACE() function performs a case-insensitive match, except when used with binary strings.
Syntax
REGEXP_REPLACE(string, pattern, replace)
Parameters
string |
Required. Specify the string to search. |
pattern |
Required. Specify the regular expression matching information. See the table below for pattern syntax. |
replace |
Required. Specify the replacement substring. |
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: REGEXP_REPLACE() examples
The example below shows the usage of REGEXP_REPLACE() function.
SELECT REGEXP_REPLACE('a b c', 'b', 'X'); Result: 'a X c' REGEXP_REPLACE('James Bond','^(.*) (.*)$','\\2, \\1'); Result: 'Bond, James' SELECT REGEXP_REPLACE('abc123ghi', '[0-9]+', ''); Result: 'abcghi' SELECT REGEXP_REPLACE('<html><head><title>title</title><body>body</body></htm>', '<.+?>',''); Result: 'titlebody'
Example: Case-sensitive replace
There are many ways in MariaDB which can be used for case-sensitive replace while using this function. See the example below:
SELECT REGEXP_REPLACE('ABC','b', '-'); Result: 'A-C' /* using COLLATE to convert string to a binary string */ SELECT REGEXP_REPLACE('ABC' COLLATE utf8_bin,'b', '-'); Result: 'ABC' /* using BINARY to convert string to a binary string */ SELECT REGEXP_REPLACE(BINARY'ABC','b', '-'); Result: 'ABC' /* forces case-sensitive using (?-i) pattern */ SELECT REGEXP_REPLACE('ABC','(?-i)b', '-'); Result: 'ABC' /* forces case-insensitive replace */ SELECT REGEXP_REPLACE('ABC' COLLATE utf8_bin,'(?i)b', '-'); Result: 'A-C' /* forces case-insensitive replace */ SELECT REGEXP_REPLACE('ABC' COLLATE utf8_bin,'(?+i)b', '-'); Result: 'A-C'
Example: Match on more than one alternative
Consider a database table called Employee with the following records:
EmpID | Name | City | Age |
---|---|---|---|
1 | John Smith | London | 25 |
2 | Marry Knight | New York | 24 |
3 | Jo Williams | Paris | 27 |
4 | Smith Fischer | Amsterdam | 30 |
5 | Aman Gupta | New Delhi | 28 |
The below query is used to replace all vowels in the records of City column with '@'. To specify more than one alternative in the pattern, | is used.
SELECT *, REGEXP_REPLACE(City, 'a|e|i|o|u', '@') AS Replaced_City FROM Employee
This will produce a result similar to:
EmpID | Name | City | Age | Replaced_City |
---|---|---|---|---|
1 | John Smith | London | 25 | L@nd@n |
2 | Marry Knight | New York | 24 | N@w Y@rk |
3 | Jo Williams | Paris | 27 | P@r@s |
4 | Smith Fischer | Amsterdam | 30 | @mst@rd@m |
5 | Aman Gupta | New Delhi | 28 | N@w D@lh@ |
Example: Matching on words
Consider the above discussed table. The below query is used to replace the first word (first name) from the Name column with 'Mr.'. To specify a range of characters, [ ] is used
SELECT *, REGEXP_REPLACE(Name, '^(.*) (.*)$', 'Mr. \\1') AS ReplacedName FROM Employee
This will produce a result similar to:
EmpID | Name | City | Age | ReplacedName |
---|---|---|---|---|
1 | John Smith | London | 25 | Mr. John |
2 | Marry Knight | New York | 24 | Mr. Marry |
3 | Jo Williams | Paris | 27 | Mr. Jo |
4 | Smith Fischer | Amsterdam | 30 | Mr. Snith |
5 | Aman Gupta | New Delhi | 28 | Mr. Aman |
❮ MariaDB - REGEXP