使用带有ISNUMERIC的派生表进行查询会导致转换失败(将varchar转换为int) [英] Query using a derived table with ISNUMERIC results in conversion failure (varchar to int)

问题描述

这是一个示例查询:

DECLARE @table table (loc varchar(10))

INSERT INTO @table VALUES
('134a'), ('123'), ('abc'), ('124')

SELECT * 
FROM (
    SELECT * FROM @table WHERE ISNUMERIC(loc) = 1
) as a
WHERE CAST(loc as INT) BETWEEN 100 AND 200

如果我有一些varchar值，并且在查询的派生表中使用ISNUMERIC将它们限制为数值，为什么会导致转换错误?

If I have some varchar values and I limit them to numeric values using ISNUMERIC in a derived table in the query, why does it result in a conversion error?:

将varchar值'134a'转换为数据类型int时转换失败.

Conversion failed when converting the varchar value '134a' to data type int.

有没有办法解决这个问题?

Is there a way around this?

推荐答案

WHERE子句首先执行.试试:

The WHERE clause executes first. Try:

DECLARE @table table (loc varchar(10)) 

INSERT INTO @table VALUES 
('134a'), ('123'), ('abc'), ('124') 

SELECT *  
FROM ( 
    SELECT * FROM @table
) as a 
WHERE ISNUMERIC(loc) = 1 and CAST(loc as INT) BETWEEN 100 AND 200 

这篇关于使用带有ISNUMERIC的派生表进行查询会导致转换失败(将varchar转换为int)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！