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;