Archives of the TeradataForum

Message Posted: Thu, 12 Jul 2012 @ 15:09:07 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Vinod,

Guess below proc will work for u. Just replace the tab1 and col1 in this proc with ur table and col before creating it. Also u can put a trim function in the update at the last (SET col1 = trim(OutString) to avoid space).

     CALL Replace_string ( '/','');

This will replace all the instances of '/' at any position in data of a field and in any row. Though all the rows will be scanned.

     REPLACE PROCEDURE Replace_string
     (
     IN SearchString VARCHAR(255)
     , IN ReplaceString VARCHAR(255)
     )
     /* This proc will update the part of the string in a field of a Table for
     all its rows and any number of instances*/
     /* 1) Just change the table name and field name before creating the proc for
     the desired tabel and field.
     2) Create the proc.
     3) CALL Replace_string ( 'Str_table _changed','from _new_str');
     4) Can also insert the new changed records to a different table if needed.
     Just comment the update and uncomment the insert below.
     */
     BEGIN
     -------------------------------------------------------------------
     DECLARE sOutString VARCHAR(30000);
     DECLARE sTmpSearchStr VARCHAR(30000);
     DECLARE iTargetStrLen INTEGER;
     DECLARE iSearchStrLen INTEGER;
     DECLARE iSearchIndex INTEGER;
     DECLARE COUNTER INTEGER;
     DECLARE TargetString VARCHAR(30000);
     DECLARE OutString VARCHAR(30000);
     --------------------------------------------------------------------
     SET sOutString = '';
     SET iSearchStrLen = CHAR_LENGTH( SearchString );
     --DEL FROM OUTPUT;
     SELECT COUNT(*) INTO :COUNTER FROM tab1 WHERE col1 LIKE
     '%'||:SearchString||'%' ;
     WHILE COUNTER > 0
     DO
     SELECT DISTINCT col1 INTO TargetString FROM tab1 WHERE col1 LIKE
     '%'||:SearchString||'%'
     QUALIFY ROW_NUMBER() OVER(ORDER BY col1 ) = :counter;
     SET iTargetStrLen = CHAR_LENGTH(TargetString );
     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;
     UPDATE tab1
     SET col1 = OutString
     WHERE TargetString = col1;
     -- INSERT INTO OUTPUT VALUES(OutString);
     -- Above statement can be changed to insert also if we want to insert the
     new records in different table say for Ex: OUTPUT
     -- CT OUTPUT
     --(
     --OUTSTR VARCHAR (1000)
     --);
     END IF;
     SET COUNTER= COUNTER - 1 ;
     END WHILE;
     END;

Thanks,

Pawan Kumar Verma

	<Prev	Next>		<<First	<Prev	Next>	Last>>