Sign inTry Free

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_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                                       |
+------+------------+-------------------------------------------+

Last modified: October 16, 2024

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK