T-SQL command to get only rows that can CAST()

I have a DB of raw data that has everything as varchar, one of the开发者_StackOverflow中文版 columns contains a price, which for the most part would convert into FLOAT easily. However, there are a few rows that have trash in that column with a bunch of characters and would not convert to float.

Is there a way to select only that rows that would convert to float (i.e. only the ones taht actually have a number)?

Thanks

Note: It is important to include the + 'e0' in the numeric test, as things like '+', '$', '.' and '3e8' would otherwise all return 1.

SELECT *
    FROM YourTable
    WHERE ISNUMERIC(YourColumn + 'e0') = 1

Have you tried the ISNUMERIC function?

SELECT *
FROM <table>
where ISNUMERIC(column)=1

See ISNUMERIC().

Try using the ISNUMERIC function first, and only when it is numeric apply the CAST.

http://msdn.microsoft.com/en-us/library/ms186272.aspx

If you're on SQL Server 2012+ and ISNUMERIC() is giving you fits, you can use TRY_PARSE() which allows you to specify the exact data type.

WHERE TRY_PARSE(price_col AS FLOAT) IS NOT NULL

As an aside, you should not be using imprecise data types for currency. You should be using NUMERIC, DECIMAL, or MONEY.