Name
regexp_replace — Replaces occurrence(s) of the matching pattern in the source_string with a specified replace_string.
Synopsis
varchar
regexp_replace
(
|
in source_string any , |
| in pattern any , | |
| in replace_string varchar , | |
| in position integer , | |
| in occurrence any , | |
in
match_parameter
integer
); |
Description
This function replaces occurrence(s) of the matching
pattern
in the source_string
with
a specified replace_string
, allowing complex
search-and-replace operations. The traditional REPLACE SQL function substitutes
one string with another. Assume your data has extraneous spaces in the text and
you would like to replace them with a single space. With the REPLACE function,
you would need to list exactly how many spaces you want to replace. However,
the number of extra spaces may not be the same everywhere in the text.
Parameters
source_string
Source string.
pattern
The regular expression to match.
replace_string
By default an empty string, ''.
position
By default this is set to 1.
occurrence
By default this is set to 0. If you specify occurrence as DB NULL value it will replace all the occurrences of the pattern in the string.
match_parameter
By default this is set to null.
Returns
This function returns the a substring matching the regular expression.
Examples
Example 24.310.
This example has three spaces between Joe and Smith. The REPLACE function's parameter specifies that two spaces should be replaced with one space. In this case, the result leaves an extra space where there were three spaces in the original string between Joe and Smith.
SELECT
REPLACE('Joe Smith', ' ', ' ') AS REPLACE_NORMAL,
REGEXP_REPLACE('Joe Smith', '( ){2,}', ' ') AS REGEXP_REPLACE;
REPLACE_NORMAL REPLACE_REGEXP
VARCHAR VARCHAR
______________________________________________
Joe Smith Joe Smith
![[Note]](images/note.png)