DB2 REPLACE Function

The DB2 REPLACE function replaces all occurrences of a search string within a source string. If the search string is not found within the source string, the original string is returned unchanged.

The syntax for the REPLACE function is shown here:

SELECT REPLACE({source_string}, {search_string}, {replace_string});

-- After DB2 v9.7 Fix Pack 5 {replace_string} is optional
SELECT REPLACE({source_string}, {search_string});

To show an example statement in use:

-- Leveraging a table column
SELECT REPLACE(LONGDESCRIPTION, 'A', 'B') FROM CATENTDESC;

The above query will take the source string, defined as a field, or explicit string, in our case the LONGDESCRIPTION value from CATENTDESC and search it for the search string ‘A’. All instances of ‘A’ will then be replaced ‘B’.

NOTE: There are few things to consider regarding the REPLACE function:

1. If the source, search, or replace string is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type it will be it will be cast to a VARCHAR data type before evaluation.
2. Staring in DB2 Version 9.7 Fix Pack 5 and later, the replace string is optional as noted in the above examples. When the expression is an empty string or the replace string has not been specified, nothing will replace the string that is removed from the source string.