|
|
|
Junior Member
 
Group: Forum Members
Last Login: 7/16/2008 5:58:49 PM
Posts: 21,
Visits: 76
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/19/2008 3:27:57 PM
Posts: 4,
Visits: 82
|
|
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
Teradata SQL Specialist V2R5
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 7/16/2008 5:58:49 PM
Posts: 21,
Visits: 76
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 3:27:09 PM
Posts: 80,
Visits: 78
|
|
Use an UDF to create an Is_Numeric() function.
http://www.teradata.com/t/go.aspx/index.html?id=130959
|
|
|
|