DB2 REPLACE Function

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.

Author: daharveyjr

I’m a solution architect responsible for the design, development, implementation, testing, and maintenance of e-commerce operations and applications using the Hybris and WebSphere Commerce product suites and other web technologies such as Java, J2EE/JEE, Spring, PHP, WordPress and more. Twitter | Facebook | LinkedIn

2 thoughts on “DB2 REPLACE Function

  1. John Beirne Reply

    Hello,
    I’m converting some VBA that uses Replace() into DB2. The VBA replace has four parameters, in my case (FieldName, StringToReplace, ReplacementString, Position), the Position being the Position in the Field Name where we want to start looking for the StringToReplace. DB2 doesn’t have a starting position, it just replaces the string anywhere in the Field. How can I specify the starting position?
    Thanks!

    • daharveyjr Post authorReply

      So there isn’t a way to specify the starting position within the REPLACE function. You’ll need to work around it, but either using another function like REGEX_REPLACE (if your version of DB2 supports it), or try and work with a SUBSTR function, to find the string you need to adjust, and then replace against that SUBSTR return value.

Leave a Reply

Your email address will not be published. Required fields are marked *