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
