CODEWITHSUNDEEP
sqlsql-serverwhere-clause
PassionateDeveloper
PassionateDeveloper

Reputation: 15138

Where execution order on castable int

I have a dataset like this:

OrderNumber | Amount     
------------------------------ 
123         | 100.00    
123a        | 100.00

What I want is a from / to in the order number like this: 

Select * from myTable where OrderNumber > 100

Of course for this I have to convert my OrderNumber in a integer: 

Select * from myTable where cast(OrderNumber as int) > 100

Now I get the error that 123N is not a convertable integer.

So I tried this: 

Select * from myTable 
where isNumberic(OrderNumber) = 1 and cast(OrderNumber as int) > 100

But I still get the same error, even the subquery 

Select * from myTable 
where isNumberic(OrderNumber) = 1

gets the correct values.

So it seems that the execution order on the different where clauses are not chained (e.g. when the first is wrong, the second and-where-clause will not be reached).

My last idea is, that I filter all not isnumeric data into a table - but this is so much overhead.

Any ideas?

Upvotes: 0

Views: 76

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

We can use TRY_CONVERT

Select * from myTable where TRY_CONVERT(INT, OrderNumber) > 100

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

If you are using SQL Server, you can use try_cast as below. Try_cast basically will try to convert your values from OrderNumber column to INT. In case it failed for any non integer value exist, it will return NULL.

SELECT * 
FROM myTable 
WHERE TRY_CAST(OrderNumber AS INT) > 100

You can check DEMO HERE

Upvotes: 1

forpas
forpas

Reputation: 164089

With a CASE statement in the WHERE clause:

Select * from myTable 
where 100 < case 
  when isNumeric(OrderNumber) = 1 then cast(OrderNumber as int)
  else 0
end

See the demo.
Results:

OrderNumber | Amount     
-------------------- 
123         | 100.00

Upvotes: 1

Related Questions