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 :)
Re: Select wholly number values from a varchar column
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.