REGEXP_REPLACE

This article will guide you on how to use the REGEXP_REPLACE() string function using a few examples.

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)

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.

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 Tables

The following example demonstrates how the REGEXP_REPLACE() function can replace certain substrings with a table.

1) First, create a table called “words“ with a single column 'words' of data type string and then insert five rows of data into the table, as shown below:

CREATE TABLE words (words string);
INSERT INTO words (words)
VALUES ('cat'), ('dog'), ('rat'), ('bat'), ('mat');

SELECT words FROM words;

The result will be as follows:

+--------+
| words  | 
+--------+
| cat    |
| dog    |
| rat 	 | 
| bat    | 
| mat	 | 
+--------+

2) Use the following query to replace all occurrences of the substring ‘at’ with ‘ot’.

SELECT words, REGEXP_REPLACE(words, 'at', 'ot') AS "New words" FROM words;

The above query will generate the following result:

+--------+-----------+
| words  | New words |
+--------+-----------+
| cat 	 | cot		 |
| dog    | dog		 |
| rat 	 | rot		 |
| bat 	 | bot		 |
| mat 	 | mot		 |
+--------+-----------+

When the query is executed, the function looks for all occurrences of the substring “a”' in the 'words' column that contain "at" and replaces them with the substring "ot”.

The result is a new column, "New words", that displays the new words with the replacement.