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 a substring matching the regular expression.
Examples
Example 24.335.
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)