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
Example24.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)