REGEXP_REPLACE

​

Overview

The REGEXP_REPLACE() function replaces all occurrences of a regular expression pattern in a string with a specified replacement string.

​

Syntax

It has the following basic syntax:

REGEXP_REPLACE(source_string, pattern, replacement, [flags])

The function takes six parameters:

• source_string: The string that we want to perform the replacement on.

• pattern: The POSIX regular expression pattern to match.

• replacement: The replacement string.

• flags: (Optional) A string that changes the matching behaviour of REGEXP_REPLACE function. See the Flags section for more details.

​

Flags

The flags parameter is an optional string that controls how the function operates. Here are the supported flags in Oxla:

​

- g

Global replacement. This flag ensures that all occurrences of the pattern are replaced.

For example, without specifying the g flag, REGEXP_REPLACE only replaces the first occurrence. In this case, only the first digit (1) is replaced with X.

SELECT REGEXP_REPLACE('ab12c', '[0-9]', 'X');

 regexp_replace 
----------------
 abX2c

By adding the g flag, all occurrences are replaced with x.

SELECT REGEXP_REPLACE('ab12c', '[0-9]', 'X', 'g');

 regexp_replace 
----------------
 abXXc

​

- i

Use this flag for case-insensitive matching. It should not be used together with the c flag.

​

- c

The REGEXP_REPLACE() function is case sensitive by default, so using the c flag has the same effect as having no flags at all. It should not be used together with the i flag.

​

Examples

​

Case #1: Basic REGEXP_REPLACE() Function

In this example, the function is used with a basic POSIX regular expression pattern:

Select REGEXP_REPLACE('The OXLA supports various data types', 'T[^ ]*', 'We') AS "Replaced_String";

The result will be:

+-----------------------------------------+
| Replaced_String                         |
+-----------------------------------------+
| We OXLA supports various data types     |
+-----------------------------------------+

In this example,  the pattern used is** “T[^ ]*”**, which matches any substring that starts with a ‘T’ character, followed by any number of non-space characters. The function replaces the matched substring with the specified replacement string “We”.

​

Case #2: Replacing Special Characters

The following example demonstrates how to replace a non-alphanumeric character in a string with an underscore.

SELECT REGEXP_REPLACE('Hello World!', '[^A-Za-z0-9 ]', '~') AS "Replaced_String";

In the above query, the second parameter is a regular expression “[^A-Za-z0-9 ]” that matches any characters that are not uppercase/lowercase letters, digits, or spaces.

The result will be:

+-------------------+
| Replaced String   |
+-------------------+
| Hello World~  	|
+-------------------+

​

Case #3: REGEXP_REPLACE() Function with Flag

The following example shows how the REGEXP_REPLACE() function can replace certain substrings with a flag defined.

1. We’ll start by creating the quotes table:

CREATE TABLE quotes (quotes_text string);
INSERT INTO quotes (quotes_text)
VALUES ('Work hard and stay hungry. Lazy people get nowhere in life.'),
       ('An excuse is a way for a LAZY person to feel better.'),
       ('The word LUCKY is how a lazy person describes someone who works hard.');

SELECT quotes_text FROM quotes;

The result will be as follows:

                              quotes_text                              
-----------------------------------------------------------------------
 Work hard and stay hungry. Lazy people get nowhere in life.
 An excuse is a way for a LAZY person to feel better.
 The word LUCKY is how a lazy person describes someone who works hard.
(3 rows)

2. Now, let’s use the REGEXP_REPLACE() function with the i flag to replace all occurrences of the word lazy with active regardless of the case sensitivity.

SELECT quotes_text, REGEXP_REPLACE(quotes_text, 'lazy', 'active', 'i') AS "New quotes" FROM quotes;

In this result, all occurrences of the word lazy (Including case insensitive) have been replaced with active.

                              quotes_text                              |                               New quotes                                
-----------------------------------------------------------------------+-------------------------------------------------------------------------
 Work hard and stay hungry. Lazy people get nowhere in life.           | Work hard and stay hungry. active people get nowhere in life.
 An excuse is a way for a LAZY person to feel better.                  | An excuse is a way for a active person to feel better.
 The word LUCKY is how a lazy person describes someone who works hard. | The word LUCKY is how a active person describes someone who works hard.
(3 rows)

words with the replacement.