Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

optimizing user-defined function "Replace"

Status
Not open for further replies.

ddiamond

Programmer
Apr 22, 2005
918
US
On As400 DB2, I created a user-defined function, udfReplace, that replaces all instances of a string within another string. The only problem is it dramatically slows down any query that uses it. Can anyone suggest anyway I can boost my performance.
Code:
CREATE FUNCTION AdmUdf.UdfReplace ( 
  Source VARCHAR(255) , 
  Find VARCHAR(255) , 
  Replace VARCHAR(255) ) 
  RETURNS VARCHAR(255)   
  LANGUAGE SQL 
  SPECIFIC AdmUdf.UdfReplace 
  DETERMINISTIC 
  READS SQL DATA 
  RETURNS NULL ON NULL INPUT 
  NO EXTERNAL ACTION 
  NOT FENCED 
  BEGIN ATOMIC 
    DECLARE s VARCHAR ( 255 ) DEFAULT '' ; 
    DECLARE iStart INT DEFAULT 1 ; 
    DECLARE i INT ; 
     
    SET i = LOCATE ( UPPER ( Find ) , UPPER ( Source ) , iStart ) ; 
     
    WHILE i > 0 DO 
      SET s = s || SUBSTR ( Source , iStart , i - 1 ) || Replace ; 
      SET iStart = iStart + i + LENGTH ( Find ) - 1 ; 
      SET i = LOCATE ( UPPER ( Find ) , UPPER ( Source ) , iStart ) ; 
    END WHILE ; 
     
    RETURN s || SUBSTR ( Source , iStart ) ; 
  END;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top