Conversion failed when converting the nvarchar value 'test' to data type int

Question

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.

Answer 1

Use TRY_CONVERT function, it's very handy.

SELECT id, 
       title 
FROM   mytable 
where  TRY_CONVERT(int, id) is not NULL 
and    TRY_CONVERT(int, id) BETWEEN 10 and 15

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:

select * into #temp2
from (
select 'test' a
union
select cast(1 as varchar) a
union 
select cast(2 as varchar) a
union 
select cast(3 as varchar) a
)z


SELECT a FROM ( 
SELECT a FROM #temp2 WHERE ISNUMERIC(a) = 1
) b
WHERE b.a BETWEEN 10 AND 15

This simple query is an alternative:

SELECT a 
FROM #temp2 
WHERE ISNUMERIC(a) = 1 
and a BETWEEN 10 AND 15

Answer 2

I should add one more way with XML style:

SELECT * 
FROM mytable
WHERE CAST(id as xml).value('. cast as xs:decimal?','int') BETWEEN 10 AND 15

Convert id to XML, convert the value in xs:decimal and then convert to integer. If there is not numeric value it will be converted into NULL.

Here you can read about XML type casting rules (link).