REGEXP_ REPLACE
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
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: October 16, 2024