Check if field is numeric, then execute comparison on only those field in one statement?

Question

This may be simple, but I am no SQL whiz so I am getting lost. I understand that sql takes your query and executes it in a certain order, which I believe is why this query does not work:

select * from purchaseorders
where IsNumeric(purchase_order_number) = 1
and cast(purchase_order_number as int) >= 7

MOST of the purchar_order_number fields are numeric, but we introduce alphanumeric ones recently. The data I am trying to get is to see if '7' is greater than the highest numeric purchase_order_number.

The Numeric() function filters out the alphanumeric fields fine, but doing the subsequent cast comparison throws this error:

Conversion failed when converting the nvarchar value '124-4356AB' to data type int.

I am not asking what the error means, that is obvious. I am asking if there is a way to accomplish what I want in a single query, preferably in the where clause due to ORM constraints.

Solution 1

does this work for you?

select * from purchaseorders
where (case when IsNumeric(purchase_order_number) = 1
       then cast(purchase_order_number as int)
       else 0 end) >= 7

Solution 2

You can do a select with a subselect

select * from (
select * from purchaseorders
where IsNumeric(purchase_order_number) = 1) as correct_orders
where cast(purchase_order_number as int) >= 7

Solution 3

try this:

select * from purchaseorders
where try_cast(purchase_order_number as int) >= 7

Comments

[Adir D]

Since it's only one digit, you could just say AND purchase_order_number > '7'

[Adir D]

Can you provide the schema for the purchaseorders table including indexes? I'm trying to build a repro - I can currently get the error message with ShyJ's example but not with yours.

[Adir D]

But the optimizer may still re-write this query and process the cast before the filter.

[ShyJ]

@AaronBertrand It really can do that? Because that would change the semantics of the query.

[Adir D]

How would that change the semantics? Filtering before or after the cast doesn't change the end result. Remember that SQL is declarative - you're telling the optimizer what result you want but you're not forcing it how to do so. When you boil it down all you would be changing is how many rows exchange between which operators.

[ShyJ]

@AaronBertrand Actually what you're saying is filtering before cast can change the end result - it may just fail. Now, I'm not a SQL Server expert but would love to see if it is mentioned anywhere in the doc or if any of such cases could be easily reproduced.

[ledgeJumper]

Yeah doing it this way still throws the error, so I believe @AaronBertrand is correct.

[Adir D]

I don't think you'll find this documented, just like Honda won't document in their owner's manual that if you try to submerge your car, the radio might not work. And no, getting an error is not "changing the end result." The plan doesn't know that you'll get an error at runtime, since it depends on data, and the plan doesn't store the data.

[Adir D]

Look at it this way: SQL Server sees that you want to filter out non-numeric values and also filter out numeric values < 7. It doesn't have to do those in the order you wrote the query - it's smart enough to parse that out into a tree and rearrange if necessary. In this case I would suspect that the engine found it more efficient to filter out the cast result first. Regarding semantics - if you have some marbles and you want to remove the steelies and the glassies, it doesn't matter if you remove the steelies first or the glassies first, the end result is the same. Right?

[bahrep]

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.