MariaDB: REGEXP_REPLACE Function
This MariaDB tutorial explains how to use the MariaDB REGEXP_REPLACE function with syntax and examples.
Description
The MariaDB REGEXP_REPLACE function is an extension of the REPLACE function. This function, introduced in MariaDB 10.0.5, will allow you to replace all occurrences of a substring in a string using regular expression pattern matching.
Syntax
The syntax for the REGEXP_REPLACE function in MariaDB is:
REGEXP_REPLACE( string, pattern, replacement )
Parameters or Arguments
- string
- The string to search.
- pattern
The regular expression matching information. It can be a combination of the following:
Value Description ^ Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression. $ Matches the end of a string. If used with a match_parameter of 'm', it matches the end of a line anywhere within expression. * 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. [ ] Used to specify a matching list where you are trying to match any one of the characters in the list. [^ ] Used to specify a nonmatching list where you are trying to match any character except for the ones in the list. ( ) 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. [..] Matches one collation element that can be more than one character. [::] Matches character classes. [==] Matches equivalence classes. \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. - replacement
- The replacement substring. All occurrences of pattern found within string are replaced with replacement.
Note
- If the REGEXP_REPLACE function does not find any occurrence of pattern, it will return the original string without any changes.
- This page applies to MariaDB 10.0.5 and higher, which uses the PCRE regex library.
- See also the REPLACE function.
Applies To
The REGEXP_REPLACE function can be used in the following versions of MariaDB:
- MariaDB 10.0.5+
Example - Match on more than one alternative
Let's start by looking how to use the | pattern with the REGEXP_REPLACE function in MariaDB.
For example:
SELECT REGEXP_REPLACE ('TechOnTheNet','a|e|i', '8');
Result: 'T8ch8nTh8N8t'
These REGEXP_REPLACE examples will replace all occurrences of the letters "a", "e", "i" or "o" in the string "TechOnTheNet" with "8". The REGEXP_REPLACE function performs a case-insensitive search so it doesn't matter whether "a", "e", "i" or "o" values are uppercase or lowercase.
Case Sensitive Search
If we wanted to perform a case-sensitive search, we need to modify our REGEXP_REPLACE function to use a binary string. This can be done using the (?-i) pattern.
SELECT REGEXP_REPLACE ('TechOnTheNet', '(?-i)a|(?-i)e|(?-i)i|(?-i)o', '8');
Result: 'T8chOnTh8N8t'
In this example, we have used the we used the (?-i) pattern to force the REGEXP_REPLACE function to perform case-sensitive pattern matching. So now the "O" character that is uppercase in "TechOnTheNet" is not replace with "8".
Table Column Search
Now let's show how to use the REGEXP_REPLACE function with a table column:
So let's say we have a contact table with the following data:
| contact_id | last_name |
|---|---|
| 1000 | Anderson |
| 2000 | Smith |
| 3000 | Johnson |
Now, let's run the following query:
SELECT contact_id, last_name, REGEXP_REPLACE (last_name, 'a|e|i|o|u', "z") AS Result FROM contacts;
These are the results that would be returned by the query:
| contact_id | last_name | Result |
|---|---|---|
| 1000 | Anderson | zndzrszn |
| 2000 | Smith | Smzth |
| 3000 | Johnson | Jzhnszn |
Example - Match on Words
Next, let's look at how to use the REGEXP_REPLACE function to replace the first word in a string.
For example:
SELECT REGEXP_REPLACE ('TechOnTheNet is a great resource', '(\\S*)', 'CheckYourMath');
Result: 'CheckYourMath is a great resource'
This example will return 'CheckYourMath is a great resource' because it will replace all non-whitespace characters as specified by (\\S*) and stop when it hits the first whitespace character (ie: space character). That will result in the word 'TechOnTheNet' being replaced with 'CheckYourMath'.
Example - Match on Digit Characters
Now, let's look next at how we would use the REGEXP_REPLACE function to match on a digit character pattern.
For example:
SELECT REGEXP_REPLACE ('7, 8, and 15 are numbers in this example', '\\d', 'abc');
Result: 'abc, abc, and abcabc are numbers in this example'
This example will replace numeric digits in the string with "abc" as specified by \\d. In this case, it will match on the numbers 7, 8, and 15.
We could change our pattern to replace only two-digit numbers.
For example:
SELECT REGEXP_REPLACE ('7, 8, and 15 are numbers in this example', '(\\d)(\\d)', 'abc');
Result: '7, 8, and abc are numbers in this example'
This example will replace all two-digit numbers with "abc" as specified by (\\d)(\\d). In this case, it will skip over the 7 and 8 numeric values and only replace the number 15.
Now, let's look how we would use the REGEXP_REPLACE function with a table column and search for a two digit number.
For example:
SELECT REGEXP_REPLACE (address, '\\d', '') FROM contacts;
In this example, we are going to remove all numbers from the address field in the contacts table. This is done by searching for all numbers using \\d and replacing with "".
Advertisements