SQL recursive REPLACE command

• David Green
• Published: 18 Dec 2003

I got frustrated that I did not have a handy SQL REPLACE() function that would work like TRIM or SUBSTRING, so I created this one. It has been very useful for cleaning up address data and other information that needed mass changing. To use it, copy the code (not the comments) and paste into interactive SQL on the iSeries (STRSQL). Execute the command and you will have added this User Defined Function to your list of functions available. Here is the syntax to use it:

REPLACE(SourceString, FindString, NewString) where SourceString is the string to search, FindSting is the string you want to replace and NewString is the string you want to put in the place of FindString.

This function is recursive so it will replace all occurrences of FindString within SourceString. The function will accommodate any string size up to 2000 characters.

 
-- FORMAT FOR THIS USER DEFINED SQL FUNCTION IS:
-- REPLACE(SOURCESTR,FINDSTR,NEWSTR)

CREATE FUNCTION REPLACE
  (SOURCESTR   VARCHAR(2000),
    FINDSTR     VARCHAR(2000),
    NEWSTR      VARCHAR(2000)  )
  RETURNS       VARCHAR(2000)
  LANGUAGE      SQL
  RETURNS NULL ON NULL INPUT

BEGIN

  DECLARE SSTRLEN    INT ;
  DECLARE POSOFSTR   INT ;
  DECLARE RIGHTLEN   INT ;
  DECLARE LEFTSTR    VARCHAR(2000) ;
  DECLARE RIGHTSTR   VARCHAR(2000) ;
  DECLARE RTNSTR     VARCHAR(2000) ;

  SET SSTRLEN   = LENGTH(SOURCESTR) ;
  SET POSOFSTR  = POSSTR(SOURCESTR,FINDSTR) ;

  CASE POSOFSTR
    WHEN 0  THEN
      SET RIGHTLEN = 0 ;
    ELSE
      SET RIGHTLEN = (SSTRLEN+1) - LENGTH(FINDSTR) - POSOFSTR ;
  END CASE ;

  CASE RIGHTLEN
    WHEN 0  THEN
      SET RIGHTSTR = '' ;
    ELSE
      SET RIGHTSTR = SUBSTR(SOURCESTR,(SSTRLEN+1)-RIGHTLEN,RIGHTLEN) ;
  END CASE ;

  SET LEFTSTR = '' ;

  CASE POSOFSTR
    WHEN 0  THEN
      SET LEFTSTR = SOURCESTR ;
    WHEN 1  THEN
      SET LEFTSTR = NEWSTR ;
    ELSE
      SET LEFTSTR = SUBSTR(SOURCESTR,1,(POSOFSTR-1))||NEWSTR ;
  END CASE;

  CASE RIGHTLEN
    WHEN 0  THEN
      SET RTNSTR = LEFTSTR ;
    ELSE
      CASE POSSTR(RIGHTSTR,FINDSTR)
        WHEN 0  THEN
          SET RTNSTR = LEFTSTR||RIGHTSTR ;
        ELSE
          SET RTNSTR = LEFTSTR||REPLACE(RIGHTSTR, FINDSTR, NEWSTR) ;
      END CASE ;
  END CASE ;

  RETURN RTNSTR;

END
  

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.