How to remove numbers and special characters from string in SQL in Teradata

My friend recently asked me how to remove special characters and numbers from values present in any column in table in Teradata. We often come across situation when we need only characters as the value and if there is anything else we should remove it. It can be some Data Mining requirement or Data Cleansing. So we can achieve this by several methods. We will discuss various possible methods in our subsequent posts. Today, we will see how we can achieve this using Stored Procedure.

Let’s create a table which will hold the data and we will call stored procedure to update the value in column in this table with desired values.

CREATE VOLATILE TABLE TBL_REPL
 (
 COL1 VARCHAR(20),
 COL2 VARCHAR(20)
 )
 ON COMMIT PRESERVE ROWS;
 

Insert some records into the table.

INSERT INTO TBL_REPL
 SEL 'AB@CD', 'AB@CD';
 
INSERT INTO TBL_REPL
 SEL 'AB@CDE#F', 'AB@CDE#F';
 
INSERT INTO TBL_REPL
 SEL 'AB@CD123%%89E#F', 'AB@CD123%%89E#F';
 
INSERT INTO TBL_REPL
 SEL 'AB@CDE#F', 'AB@CDE#F';
 

SEL * FROM TBL_REPL;

Data in table with special characters , numbers along with alphabets
Data in table with special characters , numbers along with alphabets

We will now write a stored procedure which will update COL2 with value without numbers and special characters. So after executing the stored procedure we should have only alphabets in the COL2.

REPLACE PROCEDURE PROC_TBL_REPL(IN MYTABLE VARCHAR(30), IN MYCOLUMNID VARCHAR(30), IN MYCOLUMNREP VARCHAR(30))
 BEGIN
 DECLARE DESC_ID2 VARCHAR(20);
 DECLARE DESC_DETAIL2 VARCHAR(20);
 DECLARE CNT SMALLINT;
 DECLARE TOT_LEN SMALLINT;
 DECLARE NEW_VAL VARCHAR(50);
 DECLARE CHANGED_VAL VARCHAR(50);
 DECLARE MYSQL_STR VARCHAR(10000);
 DECLARE MYSQL_STR1 VARCHAR(10000);
 DECLARE CURSOR_TST_CUR CURSOR FOR STMT1;
 SET MYSQL_STR='SEL '||MYCOLUMNID||','||MYCOLUMNREP||' FROM '||MYTABLE||'';
 PREPARE STMT1 FROM MYSQL_STR;
 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+1)
 DO
 SEL CASE WHEN (ASCII(SUBSTR(DESC_ID2,CNT,1)) BETWEEN 65 AND 90) THEN SUBSTR(DESC_ID2,CNT,1) ELSE ' ' END INTO NEW_VAL;
 SET CHANGED_VAL = CHANGED_VAL||''||NEW_VAL;
 SET CNT=CNT+1;
 END WHILE ;
 SET MYSQL_STR1='UPDATE '||MYTABLE||' SET '||MYCOLUMNREP||' = '''||CHANGED_VAL||''' WHERE '||MYCOLUMNID||'='''||DESC_ID2||'''';
 CALL DBC.SysExecSQL(MYSQL_STR1);
 END LOOP LABEL1;
 CLOSE CURSOR_TST_CUR;
 END ;
 
Run the proc by giving below command:
CALL PROC_TBL_REPL('TBL_REPL','COL1','COL2');
Now check the records in table.
SEL * FROM TBL_REPL;
Data in table with only alphabets
Data in table with only alphabets

Let’s see how we actually achieved this. We created a stored procedure which takes tablename, key column and column for which we have to replace values as input parameters. We traverse the complete string in column and check the ASCII value for characters. For alphabets, ASCII value range from 65 to 90. You may use UPPER function to convert the string to upper case and then apply ASCII function. If the ASCII value is not in range then replacing it with ‘ ‘ i.e. BLANK value. And once the complete string is traversed; we update the COL2 value with new value.

2 thoughts on “How to remove numbers and special characters from string in SQL in Teradata

Leave a Reply

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