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.
Code
-- 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.
