Check if field is numeric, then execute comparison

问题:

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.

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

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

回答3:

try this:

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