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.
Insert some records into the table.
SEL * FROM TBL_REPL;
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.
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.