Archives of the TeradataForum

Message Posted: Tue, 03 Jul 2012 @ 08:19:47 GMT

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

Hey Pawan,

Please check the procedure below:

     REPLACE PROCEDURE REPLACE_SP
     (
        IN SOURCE_STRING VARCHAR(30000)
      , IN SEARCH_STRING VARCHAR(255)
      , IN REPLACE_STRING VARCHAR(255)
      , OUT OUT_STRING VARCHAR(30000)
     )
     BEGIN
        DECLARE S_OUT_STRING VARCHAR(30000);
        DECLARE S_TMP_SEARCH_STR VARCHAR(30000);
        DECLARE I_TARGET_STR_LEN INTEGER;
        DECLARE I_SEARCH_STR_LEN INTEGER;
        DECLARE I_SEARCH_INDEX INTEGER;

        SET S_OUT_STRING = '';
        SET I_TARGET_STR_LEN = CHAR_LENGTH(SOURCE_STRING);
        SET I_SEARCH_STR_LEN = CHAR_LENGTH(SEARCH_STRING);

        IF (I_SEARCH_STR_LEN <= 0)
        THEN
           SET OUT_STRING = SOURCE_STRING;
        ELSEIF (I_TARGET_STR_LEN <= 0)
        THEN
           SET OUT_STRING = '';
        ELSEIF (I_SEARCH_STR_LEN > I_TARGET_STR_LEN)
        THEN
           SET OUT_STRING = SOURCE_STRING;
        ELSE
           SET S_OUT_STRING = '';
           SET S_TMP_SEARCH_STR = SOURCE_STRING;
           SET I_SEARCH_INDEX = POSITION(SEARCH_STRING IN S_TMP_SEARCH_STR);

           WHILE I_SEARCH_INDEX <> 0
           DO
              SET S_OUT_STRING = S_OUT_STRING || SUBSTR(S_TMP_SEARCH_STR, 1,
                 I_SEARCH_INDEX -1) || REPLACE_STRING;
              SET S_TMP_SEARCH_STR = SUBSTR(S_TMP_SEARCH_STR, I_SEARCH_INDEX +
                 I_SEARCH_STR_LEN, CHAR_LENGTH(S_TMP_SEARCH_STR) - (I_SEARCH_INDEX +
                 I_SEARCH_STR_LEN) + 1);
              SET I_SEARCH_INDEX = POSITION(SEARCH_STRING IN S_TMP_SEARCH_STR);
           END WHILE;

           SET S_OUT_STRING = S_OUT_STRING || SUBSTR(S_TMP_SEARCH_STR, 1,
                 CHAR_LENGTH(TRIM(TRAILING FROM S_TMP_SEARCH_STR)));
           SET OUT_STRING = S_OUT_STRING;
        END IF;
     END;

It's not exactly what you need as this procedure doesn't update the table but you can modify it to update the table as well.

I hope it will help ;)

Best Regards,

Sylwester.

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