ISNUMERIC函数中的错误

Question

我在SQL server中使用ISNUMERIC函数时遇到问题。

我有一个表，其中包含一个nvarchar类型的列。在这一列中，我有这样的值123、241。。。有时字符串值如LK我的查询：

WITH CTE AS (
    SELECT *
    FROM MyTable
    WHERE ISNUMERIC([Column1]) = 1
)
SELECT *
FROM CTE
WHERE CAST(cte.Column1 AS INT) > 8000

但此查询返回以下错误消息：

error converting data type nvarchar 'LK' to int

我的期望是，Common表表达式过滤所有行，哪一列是数字而不是字符串？这是正确的吗？

为什么我会收到此错误？

Answer 1

这实际上没有bug。

CTE是一个临时视图（或类似于临时视图）。所以，像查询视图一样思考我们的查询。最有可能的是，SQL将首先尝试遍历所有行，并从两个标量（isnumeric和cast）中获得结果，然后继续进行筛选。

话虽如此，它在尝试过滤演员表上的数据之前就会失败。

如果你想让它发挥作用，只需在临时表或表变量中过滤之前的数据。

Answer 2

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO
CREATE TABLE dbo.tbl (val NVARCHAR(1000))
INSERT INTO dbo.tbl
VALUES ('123'), ('234'), ('LK'), ('8001')
ALTER TABLE dbo.tbl
    ADD val2 AS CASE WHEN ISNUMERIC(val) = 1 THEN CAST(val AS INT) END
GO
SELECT *
FROM dbo.tbl
WHERE val2 > 8000

Answer 3

您可以通过添加TOP X来强制实现CTE表，以避免SQL server优化where条件使其出现类似"短路"的行为。

WITH CTE AS (
    SELECT TOP 10000000000000 
        *
    FROM (VALUES('1'), ('LK')) t(Column1)
    WHERE ISNUMERIC([Column1]) = 1
)
SELECT *
FROM CTE
WHERE CAST(cte.Column1 AS INT) > 8000

Answer 4

它与如何解析和执行查询有关。如果不能按照Devart的建议添加列，那么使用临时表可能是一种替代方法。

CREATE TABLE MyTable
(
Column1 nvarchar(50)
)
INSERT INTO MyTable
    VALUES ('123'),
    ('241'),
    ('LK'),
    ('786'),
    ('54321'),
    ('7999'),
    ('8001')
SELECT
    * INTO #TEMP
FROM MyTable
WHERE ISNUMERIC([Column1]) = 1
SELECT
    *
FROM #TEMP
WHERE CAST(Column1 AS int) > 8000
DROP TABLE #TEMP

Answer 5

主要问题是SQL是一种声明性语言，对操作的顺序要求很低。这对于允许引擎优化查询是必要的。

短路起作用的一种情况是在case语句中。所以你可以试试这样的东西：

drop table #tbl;
create table #tbl (txt varchar(10))
insert into  #tbl
 values ('123'), ('234'), ('LK'), ('9001')
select * from #tbl 
where 
 (
  case 
   when isnumeric(txt) = 1 then 
    case when cast(txt as int) > 8000 then 1 end 
  end
 ) = 1

不过，我不能告诉你演出结果如何。一般来说，您应该在可能的情况下真正使用规范化的数据——它允许SQL数据库真正发光发热：）

Answer 6

我认为下面的查询更有效：

SELECT *
FROM MyTable
WHERE ISNUMERIC([Column1]) = 1 AND  CAST(cte.Column1 AS INT) > 8000