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

Question

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?

Do you have any control over the data that is fed into this database? Just curious why you have to worry about data type to begin with. — Will Bickford, Dec 10 '11 at 07:13
It's an application that imports data into a sql table from a csv file. So I do checks to see if there is any invalid data (that would be truncated, character data in a numeric field, etc.) in the source field based on what the data type of the target field is and hilite those records in a datagridview where the user can make adjustments before they import. — Brandon Moore, Dec 10 '11 at 07:28
But yeah, if I were reading your response based on the information I provided it I woulda upvoted it too :) Well, I'll upvote it anyway because it's common sense, but unfortunately can't apply it in this scenario. — Brandon Moore, Dec 10 '11 at 07:29
Have you tried looking at output from a query analyzer? Like: http://stackoverflow.com/questions/3983386/where-is-the-query-analyzer-in-sql-server-management-studio-2008-r2 — Will Bickford, Dec 10 '11 at 07:31
You mean to get the execution plan? It's 100% table scan. Performance is okay, but I was just curious if there were any built in sql conventions I was ignoring or otherwise more concise way of doing this. Or did you mean for something else? — Brandon Moore, Dec 10 '11 at 07:36
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/5735/discussion-between-will-bickford-and-brandon-moore) — Will Bickford, Dec 10 '11 at 07:40
@WillBickford Saw your chat message but it's been half an hour so I imagine you're not there anymore. I generally like to just ignore SO when it asks me to move to chat though :) Anyway, I think the solution in my question is going to work fine, but thanks for trying to help! — Brandon Moore, Dec 10 '11 at 08:06

score 1 · Answer 1 · answered Dec 13 '11 at 20:26

1

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.

answered Dec 13 '11 at 20:26

jim31415

8,588
6
43
64

You can't prevent sql from running the convert() function before the isnumeric() function though, and if it can't convert then it's going to throw an error which is what I wanted to avoid. There aren't any particularly huge numbers in my database, but I'll give you +1 anyway for bringing to my attention the size limitation of isnumeric. – Brandon Moore Dec 15 '11 at 00:50

score 1 · Accepted Answer · answered Dec 26 '11 at 23:36

1

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

answered Dec 26 '11 at 23:36

Brandon Moore

8,590
15
65
120

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

2 Answers2