当将nvarchar值“test”转换为数据类型int时，转换失败

Context: SQL Server 2008

环境:SQL Server 2008

I have a table mytable which contains two NVARCHAR columns id, title.

我有一个表mytable，它包含两个NVARCHAR列id title。

All data in the id column are in fact numeric, except one row which contains the value 'test'.

id列中的所有数据实际上都是数值，只有一行包含值“test”。

I want to get all ids between 10 and 15 so I need SQL Server to convert id column values to INTEGER.

我希望获得10到15之间的所有id，因此需要SQL Server将id列值转换为INTEGER。

I use ISNUMERIC(id) = 1 to eliminate the non numeric values first but SQL Server is being rather weird with this query.

我首先使用ISNUMERIC(id) = 1消除非数值，但是SQL Server对这个查询有点奇怪。

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.

当将nvarchar值“test”转换为数据类型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?

内部查询消除了带有“test”id值的行，因此“in”不应该包含它。为什么SQL Server仍在尝试转换它?

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.

注:我在哪儿试过了。id为INTEGER)介于10和15之间，但也不起作用。

4 个解决方案

#1

I should add one more way with XML style:

我应该再加上一种XML风格的方式:

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.

将id转换为XML，将xs:decimal中的值转换为XML，然后将其转换为integer。如果没有数值，它将被转换为NULL。

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

在这里，您可以阅读XML类型转换规则(link)。

#2

Use TRY_CONVERT function, it's very handy.

使用TRY_CONVERT函数，非常方便。

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.

如果转换失败，TRY_CONVERT返回null。

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.

对于您的错误，我假设查询优化器在这里弄乱了一些东西。看看执行计划，可能它首先过滤了10到15之间的值。我的解决办法总是管用的。

As the other commenter said in your case the BETWEEN function is done before ISNUMERIC. Here is a simple example:

正如另一位评论者所说，在你的例子中，BETWEEN函数是在ISNUMERIC之前完成的。这里有一个简单的例子:

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

#3

Could create a new field to do the search on:

可以创建一个新的字段进行搜索:

Select id, title
    from (Select id, title, case id when 'test' then null else cast(id as int) end as trueint from mytable) n
    where n.trueint between 10 and 15

或

Select id, title
from mytable
where case isnumeric(id) when 1 then cast(id as int) else null end between 10 and 15

#4

One possibility is to force the engine to work this in two steps:

一种可能性是迫使发动机在两个步骤中工作:

DECLARE @tbl TABLE(id NVARCHAR(MAX),title NVARCHAR(MAX));
INSERT INTO @tbl 
SELECT id, title FROM mytable WHERE ISNUMERIC(id) = 1;

SELECT t.*
FROM @tbl t
WHERE CAST(t.id AS INT) BETWEEN 10 AND 15

#1