Select all rows where a varchar column converts to a decimal

Question

I have a varchar column that has generally has a decimal value, but some times there is some garbage text characters in that field.

Is it possible to filter in the WHERE clause for rows that sucessfully convert to a decimal value?

I am using sql-server 2005

Answer 1

One way is the ISNUMERIC function:

select * from YourTable where ISNUMERIC(col1) = 1

There's one gotcha: isnumeric returns 1 whenever a string can be converted to any numeric type, including money. For example, say you have rows using varying decimal separators, like 7.9 and 7,9. Both will convert to money, and isnumeric returns 1 for both of them. But only one converts to decimal, depending on the SQL Server language settings.