Problem solve Get help with specific problems with your technologies, process and projects.

SQL recursive REPLACE command

Here's a SQL REPLACE() function that works like TRIM or SUBSTRING.

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.

This was last published in December 2003

Dig Deeper on RPG iSeries programming

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close