CODEWITHSUNDEEP
sqlsql-serverperformance
Brandon Moore
Brandon Moore

Reputation: 8780

Is there a better way to write this query to short-circuit the 2nd condition?

The following is similar to a query I have that needs to cast a field to a type and evaluate it, and I want to ignore rows that have data that cannot be cast to the target data type.

select * from MyTable
where case when isnumeric(SomeField) = 1 then SomeField else null end > 1

So in the above example I am able to test for values greater than one while avoiding the conversion error that would be caused if there were any non-numeric fields in the SomeField. I'm okay with this solution, but I'm just wondering if there's a better way to do it?

Upvotes: 0

Views: 100

Answers (2)

Brandon Moore
Brandon Moore

Reputation: 8780

I've come to the conclusion that there's not a better way to do this.

Upvotes: 1

jim31415
jim31415

Reputation: 8808

I get an error "Maximum integer value exceeded" using the where criteria in your query. Though, there are some very large numbers in my database, perhaps that doesn't come into play for you.

An alternative that handles large numbers is listed below:

where isnumeric(SomeField) = 1 and convert(numeric(18,0),SomeField) > 1

The isnumeric() check must come before the convert().

Performance wise they're about the same.

Upvotes: 1

Related Questions