CODEWITHSUNDEEP
sqlt-sqlwhere-clause
stubedobedo
stubedobedo

Reputation: 61

Specify order of (T)SQL execution

I have seen similar questions asked elsewhere on this site, but more in the context of optimization.

I am having an issue with the order of execution of the conditions in a WHERE clause. I have a field which stores codes, most of which are numeric but some of which contain non-numeric characters. I need to do some operations on the numeric codes which will cause errors if attempted on non-numeric strings. I am trying to do something like

WHERE isnumeric(code) = 1
AND CAST(code AS integer) % 2 = 1

Is there any way to make sure that the isnumeric() executes first? If it doesn't, I get an error...

Thanks in advance!

Upvotes: 1

Views: 434

Answers (4)

Thomas
Thomas

Reputation: 64635

Why not simply do it using LIKE?:

Where Code Not Like '%[^0-9]%'

Btw, either using my solution or using IsNumeric, there are some edge cases which might lead one to using a UDF such as 1,234,567 where IsNumeric will return 1 but Cast will throw an exception.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453067

The only place order of evaluation is guaranteed is CASE

WHERE 
     CASE WHEN isnumeric(code) = 1 
          THEN CAST(code AS integer) % 2 
END = 1

Also just because it passes the isnumeric test doesn't guarantee that it will successfully cast to an integer. 

 SELECT ISNUMERIC('$') /*Returns 1*/

 SELECT CAST('$' AS INTEGER) /*Fails*/

Depending upon your needs you may find these alternatives preferable.

Upvotes: 5

Beth
Beth

Reputation: 9607

You could do it in a case statement in the select clause, then limit by the value in an outer select

select * from (    
    select
    case when isNum = 1 then CAST(code AS integer) % 2 else 0 end as castVal
    from (
       select
       Case when isnumeric(code) = 1 then 1 else 0 end as isNum
       from table) t
) t2
where castval = 1

Upvotes: 0

Colin Mackay
Colin Mackay

Reputation: 19175

Why not use a CASE statement to say something like:

WHERE 
CASE WHEN isnumeric(code) = 1 
THEN CAST(code AS int) % 2 = 1 
ELSE /* What ever else if not numeric */ END

Upvotes: 0

Related Questions