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 | Result | Comments |
---|---|---|
|
| Replaces the first occurrence of any character followed by |
|
| Replaces the first occurrence of any alpha character followed by |
|
| Replaces the first occurrence of any alpha character followed by |
|
| Replaces the first occurrence of |
|
| Replaces the first occurrence of |
|
| Replaces all occurrences of |
The following examples demonstrate how REGEXP_INSTR
works when the global variable regexp_format
is set to 'advanced'
.
Call to | Result | Comments |
---|---|---|
|
| Replaces the first occurrence of any digit with |
|
| Replaces the first occurrence of any alpha-numeric character with |
|
| Replaces the first occurrence of any non-digit with |
|
| Replaces the first occurrence of any non alpha-numeric character with |
|
| 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 | +------+------------+-------------------------------------------+