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:
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
.
By adding the g
flag, all occurrences are replaced with x
.
- 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:
The result will be:
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.
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:
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:
The result will be as follows:
- Now, let’s use the
REGEXP_REPLACE()
function with thei
flag to replace all occurrences of the wordlazy
withactive
regardless of the case sensitivity.
In this result, all occurrences of the word lazy
(Including case insensitive) have been replaced with active.
words with the replacement.