CODEWITHSUNDEEP
sqlsql-server
DPool34
DPool34

Reputation: 85

Issue showing results with results other than a decimal datatype

The SQL query that I was using was set up to pull all values greater than or equal to 12. 

WHERE CAST(Value AS DECIMAL(6,2)) >= 12.0

However, the result set now includes values that are not exclusively decimals. Some results are "> 40.3" instead of an actual decimal value, so I'm getting the error,

Error converting data type varchar to numeric.

I believe the error is coming from the "> 40.3" results. If I need all values greater than or equal to 12.0, how can I tweak the query to include these "> 40.3" values or the like. "> 40.3" is still technically greater than 12.0, but I'm not sure how to script this out with a non-decimal datatype ("> 40.3 is essentially a VARCHAR).

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can ignore values that are not numbers using TRY_CAST():

WHERE TRY_CAST(Value AS DECIMAL(6,2)) >= 12.0

If your only "range" specifier is >, then you can use replace():

WHERE TRY_CAST(REPLACE(Value, '> ', '') AS DECIMAL(6,2)) >= 12.0

However, I would suggest that you revise the data model so you have two columns, an upper bound and a lower bound.

Upvotes: 1

Related Questions