String Replace does not exist but can be accomplished with the following procedure:

INSTALL REQUIREMENTS:–

1) GRANT ALL ON “%TDUser%” TO “%TDUser%” WITH GRANT OPTION

2) C++ compiler, if running the TD Demo

How to replace “old” with “new” so that  ‘Hello, old world’ becomes ‘Hello, new world’

1) Logon to teradata using the %TDUser% login—

2) Create custom procedure

REPLACE PROCEDURE SysDBA.StringReplace ( IN TargetString VARCHAR(30000) — 33998 , IN SearchString VARCHAR(255) , IN ReplaceString VARCHAR(255) , OUT OutString VARCHAR(30000) )

BEGIN DECLARE sOutString VARCHAR(30000);
DECLARE sTmpSearchStr VARCHAR(30000);
DECLARE iTargetStrLen INTEGER;
DECLARE iSearchStrLen INTEGER;
DECLARE iSearchIndex INTEGER;

SET sOutString = ”;
SET iTargetStrLen = CHAR_LENGTH( TargetString );
SET iSearchStrLen = CHAR_LENGTH( SearchString );

IF (iSearchStrLen <= 0) THEN SET OutString = TargetString;
ELSEIF (iTargetStrLen <= 0) THEN SET OutString = ”;
ELSEIF (iSearchStrLen > iTargetStrLen) THEN SET OutString = TargetString;
ELSE SET sOutString = ”;

SET sTmpSearchStr = TargetString;
SET iSearchIndex = POSITION( SearchString IN sTmpSearchStr );

WHILE iSearchIndex <> 0 DO SET sOutString = sOutString || SUBSTR( sTmpSearchStr , 1 , iSearchIndex – 1 ) || ReplaceString ;

SET sTmpSearchStr = SUBSTR( sTmpSearchStr , iSearchIndex + iSearchStrLen , CHAR_LENGTH(sTmpSearchStr) – (iSearchIndex + iSearchStrLen) + 1 );
SET iSearchIndex = POSITION( SearchString IN sTmpSearchStr );

END WHILE;

SET sOutString = sOutString || SUBSTR ( sTmpSearchStr , 1 , CHAR_LENGTH(TRIM(TRAILING FROM sTmpSearchStr)) ); SET OutString = sOutString; END IF;
END;

3) Note, this can only run in another procedure, so write another procedure that calls the replace procedure. DECLARE outStr VARCHAR(33998);– CALL testload.Replace(colum1, ‘old ‘, ”, OutString)—- and get the output in the OutString variable