ISNUMERIC 函数中的错误? [英] bug in ISNUMERIC function?

问题描述

SQL-server 中的 ISNUMERIC 函数有问题.

I have a problem with ISNUMERIC Function in SQL-server.

我有一个表格，其中包含一个 nvarchar 类型的列.在本专栏中，我有这样的值 123、241、... 有时还有像 LK 这样的字符串值我的查询:

I have a table, which contains one column with nvarchar type. In this column I have such a values 123, 241, ... and sometimes string values like LK My Query:

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

但此查询返回此错误消息:

but this query returns this error message:

error converting data type nvarchar 'LK' to int

我的期望是，Common table 表达式过滤所有行，其中 Column1 是数字而不是字符串?正确吗?

my expectation is, that the Common table expression filter all rows, which Column1 are numeric and not string? Is it correct?

为什么我会收到此错误?

Why do I receive this error?

推荐答案

这实际上没有错误.

CTE 是一个临时视图(或充当临时视图).所以想想我们的查询就像查询视图一样.很可能 SQL 将首先尝试遍历所有行并从两个标量(isnumeric 和强制转换)中获取结果，然后进行过滤.

The CTE is a temporary view (or acts like a temporary view). So think on our query like querying a view. Most likely SQL will try first to go through all rows and get results from both scalars (isnumeric and the cast) then proceed to the filtering.

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

That being said, it will fail way before it will try to filter the data on the cast.

如果你想让它工作，只需在临时表或表变量中过滤数据.

If you want to make it work just filter the data before in a temporary table or in a table variable.

这篇关于ISNUMERIC 函数中的错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！