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 ofREGEXP_REPLACE
function. See the Flags section for more details.
- The function returns
NULL
if there are no input rows or NULL
values. - If the regular expression pattern is not found in the string, then the
REGEXP_REPLACE()
function will return the original string.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.
- For flags
ci
, the regex will be case-insensitive. . - For flags
ic
, the regex will be case-sensitive.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.
- 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)
- Now, let’s use the
REGEXP_REPLACE()
function with thei
flag to replace all occurrences of the wordlazy
withactive
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.