Oracle PL/SQL Programming, Third Edition

Name

REPLACE

Synopsis

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 (string1 IN VARCHAR2, match_string IN VARCHAR2 
                  [, replace_string IN VARCHAR2])
RETURN VARCHAR2

If you do not specify the replacement string, then REPLACE simply removes all occurrences of the match_string in 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 ...