Filter unwanted characters from a column in Teradata

Question

I have a Phone number column in my table with values only being numbers and no special characters. for one of the column I got a value coming in as ":1212121212".

I will need to filter this record and any records coming in with any special characters in teradata. Can anyone help on this.

I have tried the below solutions but it is not working

where (REGEXP_SUBSTR(column_name, '[0-9]+')<>1 or column_name is null )

Instead of checking if there is special character in string, you can check if the value contains only number — Hana, Jun 14 '22 at 07:44

Jonas Metzler · Answer 1 · 2022-06-14T11:45:11.320

0

In MS SQL Server DB's, you can use TRYCAST to find those entries having non numeric characters:

SELECT column_name 
FROM yourtable
WHERE TRY_CAST(column_name AS INT) IS NULL;

In Teradata DB's, you can use TO_NUMBER:

SELECT column_name 
FROM yourtable
WHERE TO_NUMBER(column_name) IS NULL;

If you want to stay close to your attempt, can use LIKE to find not numeric entries:

SELECT column_name 
FROM yourtable
WHERE column_name LIKE '%[^0-9]%';

Note this could get slow when your table has very many rows.

edited Jun 14 '22 at 11:45

answered Jun 14 '22 at 07:47

Jonas Metzler

4,517
1
5
17

ISNUMERIC is a terribly misleading name and function - not recommended at all. – SMor Jun 14 '22 at 11:18
You're right, TRYCAST should work better here. Updated the answer. – Jonas Metzler Jun 14 '22 at 11:38

score 0 · Accepted Answer · answered Jun 16 '22 at 07:57

0

Thanks Jonas. Since I need only numeric values and the length should be 10, I tried the below and it worked. This would ignore all the additional special characters.

(regexp_similar(Column,'[0-9]{10}')=1)

answered Jun 16 '22 at 07:57

Vijayanarayanan

1
3

Filter unwanted characters from a column in Teradata

2 Answers2