CODEWITHSUNDEEP
sql-servert-sql
Jorge Guzman
Jorge Guzman

Reputation: 499

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 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

Upvotes: 2

Views: 615

Answers (4)

Bacon Bits
Bacon Bits

Reputation: 32170

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.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

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

Upvotes: 4

Shawn
Shawn

Reputation: 9402

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

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

Upvotes: 0

garnertb
garnertb

Reputation: 9584

Have you tried the ISNUMERIC function?

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

See ISNUMERIC().

Upvotes: 1

Related Questions