REGEXP_REPLACE
The REGEXP_REPLACE
function finds and replaces text column substrings using constant regexp-based patterns with constant replacement strings.
For related information, see the following:
Syntax
REGEXP_REPLACE(input, pattern [, replacement [, position [, occurrence]]])
Arguments
The following table shows the REGEXP_REPLACE
arguments:
Parameter |
Description |
---|---|
|
The replaced input value. |
|
The pattern that identifies which substrings to replace. |
|
The replacement string. |
|
Indicates the 1-based offset where the pattern search starts. |
|
(Optional) Sets a specific occurrence to replace. Using |
Test Patterns
Test Pattern |
Description |
---|---|
|
Match the beginning of a string |
|
Match the end of a string |
|
Match any character (including whitespace such as carriage return and newline) |
|
Match the preceding pattern zero or more times |
|
Match the preceding pattern at least once |
|
Match the preceding pattern once at most |
|
Match either |
|
Match zero or more instances of the sequence |
|
Match the preceding pattern exactly two times |
|
Match the preceding pattern between two and four times |
|
Matches any character that is (or is not when negated with |
Returns
The REGEXP_REPLACE
function returns the replaced input value.
Notes
The test pattern must be a literal string.
Example
CREATE TABLE test(country_name TEXT);
INSERT INTO test values('SWEDEN');
SELECT REGEXP_REPLACE(country_name, 'WEDE', 'PAI') FROM test;
Output:
country_name|
------------+
SPAIN |