REPLACE function

REPLACE is a string manipulation function that manipulates all string data types (BIT, BLOB, and CHARACTER), and replaces parts of a string with supplied substrings.

Syntax

REPLACE returns a string consisting of the source string, with each occurrence of the search string replaced by the replace string. The parameter strings can be of the CHARACTER, BLOB, or BIT data types, but all three must be of the same type.

If any parameter is NULL, the result is NULL.

The search process is single pass from the left and disregards characters that have already been matched.

If you do not specify the replace string expression, the replace string uses the default value of an empty string, and the behavior of the function is to delete all occurrences of the search string from the result.

The following examples give the results shown:

      REPLACE('ABCDABCDABCDA', 'A', 'AA')
      -- RESULT = AABCDAABCDAABCDAA

The above example shows that replacement is single pass. Each occurrence of A is replaced by AA but these are not then expanded further.

      REPLACE('AAAABCDEFGHAAAABCDEFGH', 'AA', 'A')  
      -- RESULT = AABCDEFGHAABCDEFGH

This example shows that after characters are matched, they are not considered further. Each occurrence of AA is replaced by A. The resulting AA pairs are not matched.

     REPLACE('AAAAABCDEFGHAAAABCDEFGH', 'AA', 'XYZ') 
      -- RESULT = XYZXYZABCDEFGHXYZXYZBCDEFGH

This last example shows that matching is from the left. The first four As are matched as two pairs and replaced. The fifth A is not matched.