Teradata Replace Function Alternative

teradata

Many people asked me if there is any alternative method in Teradata which can replicate REPLACE functionality in Teradata. Basically, the intent is to REPLACE some character with some other character in all the column values in a table. There is a REPLACE & OREPLACE function available in Teradata however many people find that the REPLACE function is not available in Teradata environment they are working. So I have created a Stored Procedure, which can replicate the same functionality of REPLACE function. Below are the details:

I created a Table and inserted some records in the table. For this example, I have tested REPLACING SPACE BY ‘-‘ symbol.

/* CREATE A TEST TABLE */

CREATE TABLE TBL_REPLACE_CUR
(
DESC_ID INTEGER,
DESC_DETAIL VARCHAR(50),
DESC_NEW VARCHAR(30)
);

/* INSERT SOME DATA INTO THE TABLE */

INSERT INTO TBL_REPLACE_CUR (DESC_ID,DESC_DETAIL)
SEL 1,’HELLO WORLD. THIS IS TERADATA’;

INSERT INTO TBL_REPLACE_CUR (DESC_ID,DESC_DETAIL)
SEL 2,’MOTIVE IS TO REPLACE SPACE WITH -‘;

INSERT INTO TBL_REPLACE_CUR (DESC_ID,DESC_DETAIL)
SEL 3,’NOSPACE’;

INSERT INTO TBL_REPLACE_CUR (DESC_ID,DESC_DETAIL)
SEL 4,’ FIRST SPACE’;

INSERT INTO TBL_REPLACE_CUR (DESC_ID,DESC_DETAIL)
SEL 5,’ FIRST & LAST SPACE ‘ ;

Now data in Table looks like:
SEL * FROM TBL_REPLACE_CUR;

DESC_ID

DESC_DETAIL

DESC_NEW

1

HELLO
WORLD. THIS IS TERADATA

NULL

2

MOTIVE IS TO REPLACE SPACE WITH –

NULL

3

NOSPACE

NULL

4

 FIRST SPACE

NULL

5

 FIRST & LAST SPACE

NULL

Below Procedure is used REPLACE functionality in Teradata. We open a CURSOR to the table and for each row, we perform the logic for replacing the space with ‘-‘.

REPLACE PROCEDURE PROC_REPLACE_CUR()
BEGIN
DECLARE DESC_ID2 INTEGER;
DECLARE DESC_DETAIL2 VARCHAR(50);
DECLARE CNT SMALLINT;
DECLARE TOT_LEN SMALLINT;
DECLARE C_POSITION SMALLINT;
DECLARE NEW_VAL VARCHAR(50);
DECLARE CHANGED_VAL VARCHAR(50);
DECLARE CURSOR_TST_CUR CURSOR FOR
SEL DESC_ID, DESC_DETAIL FROM TBL_REPLACE_CUR ORDER BY 1 ;
OPEN CURSOR_TST_CUR;
LABEL1:
LOOP
FETCH CURSOR_TST_CUR INTO DESC_ID2,DESC_DETAIL2;
IF (SQLSTATE = ‘02000’) THEN
LEAVE LABEL1;
END IF ;
SET CNT=1;
SET NEW_VAL=”;
SET CHANGED_VAL=”;
SEL CHAR_LENGTH(DESC_DETAIL2) INTO TOT_LEN;
LABEL:
WHILE (CNT < TOT_LEN)
DO
SEL CASE
WHEN INDEX( SUBSTR(DESC_DETAIL2,CNT,TOT_LEN),’ ‘)<>0 THEN INDEX( SUBSTR(DESC_DETAIL2, CNT,TOT_LEN),’ ‘)
ELSE 0
END INTO C_POSITION;
SEL CASE
WHEN C_POSITION <>0 THEN SUBSTR(DESC_DETAIL2,CNT,C_POSITION-1)
ELSE SUBSTR(DESC_DETAIL2,CNT)
END INTO NEW_VAL;

IF CNT<>1 THEN
SET CHANGED_VAL=CHANGED_VAL||’-‘||NEW_VAL;
ELSE
SET CHANGED_VAL=NEW_VAL;
END IF ;

IF C_POSITION <> 0 THEN
SET CNT=CNT+ C_POSITION;
ELSE
SET CNT=TOT_LEN;
END IF ;

END WHILE ;

UPDATE TBL_REPLACE_CUR
SET
DESC_DETAIL = :CHANGED_VAL,
DESC_NEW=’WORKING FINE FOR ALL ROWS’
WHERE DESC_ID = : DESC_ID2;

END LOOP LABEL1;

CLOSE CURSOR_TST_CUR;
END ;

To call this Procedure :
CALL PROC_REPLACE_CUR();

You may wish to change the logic as per your requirement.
Feel free to leave comments if you find any issue in understanding the PROC.

Leave a Reply

Your email address will not be published. Required fields are marked *