Skip to main content

SingleStoreDB v8.1

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 singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE regexample_table(id INT, text_field TEXT);

INSERT INTO regexample_table VALUES (1, 'the'), (2, 'athe'), (3, 'abthe'), 
(4, 'abcthe'), (5, 'thethe');

SELECT id, text_field, REGEXP_REPLACE(text_field, '..the', 'to') FROM regexample_table
ORDER BY id;
****
+------+------------+-------------------------------------------+
| id   | text_field | REGEXP_REPLACE(text_field, '..the', 'to') |
+------+------------+-------------------------------------------+
|    1 | the        | the                                       |
|    2 | athe       | athe                                      |
|    3 | abthe      | to                                        |
|    4 | abcthe     | ato                                       |
|    5 | thethe     | tto                                       |
+------+------------+-------------------------------------------+
In this section: