Execute a WHERE clause before another one

2019-08-09 01:33发布

问题:

I have the following statement

SELECT * FROM foo
WHERE LEN(bar) = 4 AND CONVERT(Int,bar) >= 5000

The values in bar with a length of exactly 4 characters are integers. The other values are not integers and therefore it throws an conversion exception, when trying to convert one of them to an integer.

I thought it's enough to put the LEN(bar) before the CONVERT(Int,bar) >= 5000. But it's not.

How can I kind of prioritize a specific where clause? In my example I obviously want to select all values with a length of 4, before converting and comparing them.

回答1:

6 answers and 5 of them don't work (for SQL Server)...

SELECT *
  FROM foo
 WHERE CASE WHEN LEN(bar) = 4 THEN
       CASE WHEN CONVERT(Int,bar) >= 5000 THEN 1 ELSE 0 END
       END = 1;

The WHERE/INNER JOIN conditions can be executed in any order that the query optimizer determines is best. There is no short-circuit boolean evaluation.

Specifically for your question, since you KNOW that the data with 4-characters is a number, then you can do a direct lexicographical (text) comparison (yes it works):

SELECT *
  FROM foo
 WHERE LEN(bar) = 4 AND bar > '5000';


回答2:

try this

SELECT bar FROM 
(
SELECT  CASE
       WHEN LEN(bar) = 4 THEN   CAST( bar as int) 
       ELSE  CAST(-1 as int) END bar
FROM Foo
) Foo
WHERE bar>5000