Varchar列上使用IsNumeric的SQL语法错误

I am trying to execute a SQL-Statement

我正在尝试执行一个sql语句

SELECT * 
FROM Menu
WHERE ISNumeric(Data) = 1
AND Data = 296

where the Data field may contain numeric or non-numeric contents. But I am getting syntax error something like

数据字段可以包含数值或非数值内容。但是我有一些语法错误。

Syntax error on converting the varchar-value '/home' in a column of Data type int.

在数据类型int的列中转换var -value '/home'的语法错误。

3 个解决方案

#1

since DATA column is varchar, you need to enclosed the value (296) with single quote

由于数据列是varchar，所以需要用单引号括起来(296)。

SELECT * 
FROM   Menu
WHERE  ISNumeric(Data) = 1 AND Data = '296'

you can still directly query without using ISNUMERIC here, and increases performance since you don;t have to check every record for numeric values.

在这里，您仍然可以不使用ISNUMERIC直接查询，并且可以提高性能，因为您不必检查每个记录的数值。

Using IsNumeric is pointless, I guess, in your situation.

我想，在你的情况下，使用IsNumeric是没有意义的。

SELECT * 
FROM   Menu
WHERE  Data = '296'

#2

Unfortunately, SQL doesn't guarantee short circuit evaluation, therefore the IsNumeric(Data) filter won't prevent evaluation of the second predicate, Data = 296, if the first condition is false.

不幸的是，SQL不保证短路评估，因此如果第一个条件为false, IsNumeric(Data)过滤器不会阻止第二个谓词Data = 296的评估。

What you can do however is leave the comparison as a char literal, e.g.

但是，您可以做的是将比较保留为char literal，例如。

SELECT * 
FROM Menu
WHERE ISNumeric(Data) = 1
AND Data = '296';

Out of interest, why would you need to do the IsNumeric check if you are comparing to an exact numeric value?

出于兴趣，如果要与一个确切的数值进行比较，为什么需要进行IsNumeric检查?

#3

Just to add to @StuartLC's answer, one workaround for this is by using a subquery to obtain all the numeric values first, e.g.:

为了补充@StuartLC的答案，一个解决方法是使用子查询首先获取所有的数值，例如:

SELECT *
FROM   (
    SELECT CAST(Data AS int) AS Data, 
           -- Other fields
    FROM   Menu
    WHERE  ISNumeric(Data) = 1
) subquery
WHERE subquery.Data = 296

Although note you will need to be sure you won't have decimal numbers, or scientific notation, or that sort of thing, as IsNumeric will return 1 for these. This question will help you resolve issues around that...

尽管要注意，您需要确保不会有十进制数、科学符号或类似的东西，因为IsNumeric会为这些返回1。这个问题将帮助你解决与之相关的问题。

#1