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 literal 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                                       |
+------+------------+-------------------------------------------+
Was this article useful?