Context: SQL Server 2008
I have a table mytable
which contains two NVARCHAR
columns id
, title
.
All data in the id
column are in fact numeric, except one row which contains the value 'test'.
I want to get all ids between 10 and 15 so I need SQL Server to convert id column values to INTEGER
.
I use ISNUMERIC(id) = 1
to eliminate the non numeric values first but SQL Server is being rather weird with this query.
SELECT
in.*
FROM
(SELECT
id, title
FROM
mytable
WHERE
ISNUMERIC(id) = 1) in
WHERE
in.id BETWEEN 10 AND 15
This query causes the following error:
Conversion failed when converting the nvarchar value 'test' to
data type int.
The inner query eliminates the row with the 'test' id value so 'in' shouldn't contain it. Why is SQL Server still trying to convert it?
Am I missing something? How can I get around this?
P.S. I tried WHERE CAST(in.id AS INTEGER) BETWEEN 10 AND 15
but didn't work either.
Best Answer
Use TRY_CONVERT function, it's very handy.
TRY_CONVERT returns null if the conversion fails.
And for you error, I suppose that the Query Optimizer messes something up here. Take a look at the execution plan, maybe it's filtering values between 10 and 15 at the first place. My solution will always work.
As the other commenter said in your case the BETWEEN function is done before ISNUMERIC. Here is a simple example:
This simple query is an alternative: