The REGEXP_REPLACE() function replaces all occurrences of a regular expression pattern in a string with a specified replacement string.
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.
- 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.
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".
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:
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:
The result will be as follows:
2) Use the following query to replace all occurrences of the substring ‘at’ with ‘ot’.
The above query will generate the following result:
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.