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.
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';
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