The REPLACE function returns a string in which all occurrences of a specifiedmatch string are replaced with a replacement string. REPLACE is useful for searching a pattern of characters, and then changing all instances of that pattern in a single function call. The specification of the REPLACE function is:
FUNCTION REPLACE (
match_stringIN VARCHAR2 [,
replace_stringIN VARCHAR2]) RETURN VARCHAR2
If you do not specify the replacement string, then REPLACE simply
removes all occurrences of the
string1. If you specify neither a match string
nor a replacement string, REPLACE returns NULL.
Here are several examples using REPLACE:
Remove all instances of the letter “C” in the string “CAT CALL”:
REPLACE ('CAT CALL', 'C') --> 'AT ALL'
Because we did not specify a replacement string, REPLACE changed all occurrences of “C” to NULL.
Replace all occurrences of “99” with “100” in the following string:
REPLACE ('Zero defects in period 99 reached 99%!', '99', '100') --> 'Zero defects in period 100 reached 100%!'
Handle occurrences of a single quote within a query criteria string. The single quote is a string terminator symbol, indicating the start and/or end of the literal string. I once ran into this requirement when building query-by-example strings in Oracle Forms. If the user enters a string with a single quote in it, such as:
Customer didn't have change.
and then the program concatenates that string into a larger string, the resulting SQL ...