REGEXP_ REPLACE
On this page
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_
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_
supports the extended and advanced regular expression formats.regexp_
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 theparameters
argument,replace_
replaces all occurrences of the regular expression pattern.string
-
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
ori
is not specified, performs case-sensitive matching.
-
Examples
The following examples demonstrate how REGEXP_
works when the global variable regexp_
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_
works when the global variable regexp_
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. |
Using REGEXP_ REPLACE
With a Table
The following example shows how to call REGEXP_
once per table row.
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_tableORDER 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 |
+------+------------+-------------------------------------------+
Last modified: August 9, 2023