You are viewing an older version of this section. View current production version.
REGEXP_REPLACE
Searches a string for the first occurrence of a regular expression pattern and replaces that occurrence with a replacement string. If no occurrences are found, the search string is returned as is.
If you call REGEXP_REPLACE and specify g in the parameters argument, the function performs a search and replace of all occurrences of a regular expression pattern with a replacement string.
REGEXP_REPLACE supports the extended and advanced regular expression formats. You can set the global variable regexp_format to specify which format you want to use.
Syntax
REGEXP_REPLACE (source_string, pattern, replace_string [, parameters]))
source_string
A string expression, such as a column name, to be searched.
pattern
A string representing a SQL standard regular expression.
replace_string
A string that replaces the first occurrence of the regular expression pattern. If you specify g in the parameters argument, replace_string replaces all occurrences of the regular expression pattern.
parameters
Optional. If present, has one or more of the following values.
c: Performs case-sensitive matching.
i: Performs case-insensitive matching.
g: Replaces all occurrences of a regular expression pattern with a replacement string.
If c or i is not specified, performs case-sensitive matching.
Examples
The following examples demonstrate how REGEXP_REPLACE works when the global variable regexp_format is set to 'extended'.
Call to REGEXP_REPLACE |
Result | Comments |
|---|---|---|
SELECT REGEXP_REPLACE('aadog','.dog','cat'); |
acat |
Replaces the first occurrence of any character followed by dog with cat. |
SELECT REGEXP_REPLACE('aadog','[:alpha:]dog','cat'); |
acat |
Replaces the first occurrence of any alpha character followed by dog with cat. |
SELECT REGEXP_REPLACE('aadog','\\wdog','cat'); |
acat |
Replaces the first occurrence of any alpha character followed by dog with cat. |
SELECT REGEXP_REPLACE('catcatcat','c','b'); |
batcatcat |
Replaces the first occurrence of c with b. |
SELECT REGEXP_REPLACE('CatCatCat','c','b','i'); |
batCatCat |
Replaces the first occurrence of c with b. Does a case-insensitive search. |
SELECT REGEXP_REPLACE('CatCatCat','c','b','ig'); |
batbatbat |
Replaces all occurrences of c with b. Does a case-insensitive search. |
The following examples demonstrate how REGEXP_INSTR works when the global variable regexp_format is set to 'advanced'.
Call to REGEXP_REPLACE |
Result | Comments |
|---|---|---|
SELECT REGEXP_REPLACE('dog58','\\d','A'); |
dogA8 |
Replaces the first occurrence of any digit with A. |
SELECT REGEXP_REPLACE('%26dog','\\w','B'); |
%B6dog |
Replaces the first occurrence of any alpha-numeric character with B. |
SELECT REGEXP_REPLACE('dog58','\\D','A'); |
Aog58 |
Replaces the first occurrence of any non-digit with A. |
SELECT REGEXP_REPLACE('%26dog','\\W','B'); |
B26dog |
Replaces the first occurrence of any non alpha-numeric character with B. |
SELECT REGEXP_REPLACE('apple banana','(.+) (.+)','\\2 \\1'); |
banana apple |
Uses backreferences. The nth group of parentheses is the nth backreference. Replaces the first backreference with the second, and vice-versa. |
Using REGEXP_REPLACE With a Table
The following example shows how to call REGEXP_REPLACE once per table row. This example uses extended regular expression matching. Advanced regular expression matching could be used as well.
SET GLOBAL regexp_format = 'extended';
DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
USE memsql_docs_example;
CREATE TABLE example_table(id INT, text_field TEXT);
INSERT INTO example_table VALUES (1, 'the'), (2, 'athe'), (3, 'abthe'),
(4, 'abcthe'), (5, 'thethe');
SELECT id, text_field, REGEXP_REPLACE(text_field, '..the', 'to') FROM example_table
ORDER BY id;
Output
+------+------------+-------------------------------------------+
| id | text_field | REGEXP_REPLACE(text_field, '..the', 'to') |
+------+------------+-------------------------------------------+
| 1 | the | the |
| 2 | athe | athe |
| 3 | abthe | to |
| 4 | abcthe | ato |
| 5 | thethe | tto |
+------+------------+-------------------------------------------+
