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

Question

背景： SQL Server 2008

我有一张表mytable，里面包含两列NVARCHAR类型的列id和title。

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

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

我使用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

这个查询导致以下错误:

在将nvarchar类型的值“test”转换为int数据类型时转换失败。

内部查询已经排除了具有'id'值为“test”的行，因此'in'中不应包含它。为什么SQL Server仍然尝试将其转换？

我有什么遗漏吗？我该如何解决这个问题？

附言：我尝试过WHERE CAST(in.id AS INTEGER) BETWEEN 10 AND 15，但也没有起作用。

- Bobe Kryant

---

不能保证 SQL Server 会在执行 "in between" 前先执行 "isnumeric" 操作，因此该函数始终具有危险性。 - GuidoG

请添加 SQL Server 版本。 - Joe Taras

@GuidoG 是的，那可能就是原因了。有什么解决办法吗？ - Bobe Kryant

cybernetic87的回答很完美，但是我了解你使用的是SQL Server 2008？这可能会成为一个问题。 - GuidoG

@GuidoG 是的，我忘了提到它。我编辑了我的问题。 - Bobe Kryant

你能为你的查询添加执行计划吗？ - Esoteric Screen Name

Answer 1

使用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 将返回 null。

对于您的错误，我认为查询优化器在这里搞错了什么。查看执行计划，也许它在第一次过滤 10 到 15 之间的值。我的解决方案总是有效的。

如另一位评论者在您的情况下所说，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

这个简单的查询是一个替代方案：

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

- cybernetic87

---

使用 isnumeric 始终存在风险，因为您无法控制执行计划，它可能在条件之间被调用。下面的方法更好。 - GuidoG

这本来是完美的选择，但不幸的是我的数据库兼容级别为 SQL Server 2008（100），而更改它对我来说不是选项。那么是否有其他替代方案可以考虑呢？ - Bobe Kryant

TRY_CONVERT 函数在 SQL Server 2012 及以上版本中可用，不支持 SQL Server 2008。 - marc_s

问题最初没有标记与 SQL Server 版本相关的信息。 - cybernetic87

Answer 2

我应该添加一种XML样式的方式：

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

将id转换为XML格式，将值转换为xs:decimal，然后再转换为integer。如果没有数字值，则会转换为NULL。

您可以在此处阅读有关XML类型转换规则的信息（链接）。

- gofr1

---

我测试过了，它可以正常工作。由于一些 ID 溢出的问题，我不得不将“int”更改为“bigINT”，但仍然是一个很好的解决方案！谢谢！ - Bobe Kryant

非常愉快！:) - gofr1

很棒的解决方案！我还没有想到过... 我给一个赞。 - Shnugo

Answer 3

可以创建一个新字段来进行搜索：

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

- cloudsafe

---

我喜欢这个想法，我支持+1，但最好使用类似于CASE WHEN ISNUMERIC(id)=1 THEN id ELSE NULL END AS validNumber的东西，然后WHERE CAST(validNumber AS INT) BETWEEN ...。 - Shnugo

谢谢@Shnugo。我重新考虑了一下，只使用WHERE子句中的CASE语句。 - cloudsafe

Answer 4

一种可能的方法是强制引擎分两步完成此操作:

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

- Shnugo