Archives of the TeradataForum

Message Posted: Thu, 23 Mar 2006 @ 19:38:15 GMT

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

Hi,

I have implemented Stored Procedure approach suggested by Khan ...it may need fine tuning but it's working fine so thought to share with everyone

     REPLACE PROCEDURE SP_REMOVESPACE()
     BEGIN
     DECLARE SRC_NAME VARCHAR(75);
     DECLARE SRC_NAME_STD VARCHAR(75);
     DECLARE SRC_COUNTER INTEGER;
     DECLARE REC_COUNTER INTEGER;
     DECLARE SRC_NAME_UPD VARCHAR(75);
     DECLARE SRC_TEMP  VARCHAR(75);
     L1:
     FOR CustCursor AS c_customer CURSOR FOR
     Select SOURCE_CUST_NAME, SOURCE_CUST_NAME_STD
     >From REMOVE_SPACE
     DO
     SET SRC_NAME= CustCursor.SOURCE_CUST_NAME;
     SET SRC_NAME_STD  = CustCursor.SOURCE_CUST_NAME_STD;

     IF SRC_NAME_STD ='' THEN
      SET SRC_NAME_STD=SRC_NAME;
      SET SRC_COUNTER = CHAR_LENGTH (SRC_NAME_STD);
      SET REC_COUNTER=0;
      SET SRC_NAME_UPD='';
      SET SRC_TEMP='';

      WHILE (REC_COUNTER<>SRC_COUNTER )  DO

       SET SRC_TEMP= SUBSTR(SRC_NAME_STD,REC_COUNTER, 1);

       IF (SRC_TEMP<>' ') THEN
       SET SRC_NAME_UPD=SRC_NAME_UPD || SRC_TEMP;
       END IF;

       SET REC_COUNTER=REC_COUNTER+1;
      END WHILE;

     UPDATE REMOVE_SPACE
     SET SOURCE_CUST_NAME_STD = :SRC_NAME_UPD
     WHERE SOURCE_CUST_NAME=:SRC_NAME;

     END IF;
     END FOR L1;
     END;

My Test Table Structrue was

     CREATE SET REMOVE_SPACE ,FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           SOURCE_CUST_NAME VARCHAR(75) CHARACTER SET LATIN NOT CASESPECIFIC,
           SOURCE_CUST_NAME_STD VARCHAR(75) CHARACTER SET LATIN NOT
     CASESPECIFIC)
     PRIMARY INDEX ( SOURCE_CUST_NAME );

- Vishal Parkhe

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