Select wholly number values from a varchar column

Analytics
Enthusiast

Select wholly number values from a varchar column

I am trying to select the value of a varchar(32) column if it contains entirely numeric characters. The varchar column contains values of that could be a mixture of numeric and alphanumeric characters (no special characters).

The rows I need to select are the ones that are entirely numeric, and I was thinking a CAST function could be used t return rows that could successfully be CAST as float. Something like;

WHERE NOT((CAST( as FLOAT) IS NULL)

This doesn't work, and I'm going to be processing millions of rows of data, so I'd like to avoid LIKE functions or anything that would take a long time :)

Any ideas?

Thanks

Tim

3 REPLIES

Re: Select wholly number values from a varchar column

Tim,

I could not realize how to do it only using SQL statements, but I think that you can solve it using FastExport and FastLoad utilities.
First export distinct values from your column to a flat file padding blanks at right, so all rows will have 32 bytes length.
Create a staging table with 3 columns, ex. col1 decimal(18,0), col2(decimal(14,0), col3 varchar(32).
(col1 and col2 stand for keep the entire 32 bytes since my teradata system does not support decimal(38,0) data types)

Create a FastLoad script to import the first 18 characters to col1 and the last 14 characters to col2 and the whole 32 chars to col3.
Only the rows that contain numeric values will be casted to the decimal data type and inserted in your stage table.
The rows that contain alphanumeric data will be sent to the Fastload error table.

If your Teradata database supports the decimal(38,0) data type you can use only 1 column for the casting purposes.

This might work for you.

Regards,

Cruz
Enthusiast

Re: Select wholly number values from a varchar column

ahhh, yes! Nice idea.

This method should work quite well because although the varchar is 32 nearly 100% of the time the numbers are actually 12-14 characters in length (phone numbers), so I could use just one column.

Thank you for your help

Tim

Teradata Employee

Re: Select wholly number values from a varchar column

Use an UDF to create an Is_Numeric() function.

http://www.teradata.com/t/go.aspx/index.html?id=130959