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.
The ISNUMERIC() function tests whether an expression is numeric. This function returns 1 if the expression is numeric, otherwise it returns 0.
SQL Greater than or equal to ( >= ) operator The greater than equal to operator is used to test whether an expression (or number) is either greater than or equal to another one.
isNumeric() method: It is used to check whether the given argument is a numeric value or not. If it is numeric then it returns true Otherwise returns false.
In SQL Server, we can use the ISNUMERIC() function to return numeric values from a column. We can alternatively run a separate query to return all values that contain numeric data.
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
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
try this:
select * from purchaseorders
where try_cast(purchase_order_number as int) >= 7
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With