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.