使用带有 ISNUMERIC 的派生表进行查询会导致转换失败（varchar 到 int）

发布于 2025-01-02 20:42:56 字数 439 浏览 1 评论 0原文

这是一个示例查询：

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 将它们限制为数值，为什么会导致转换错误？：

将 varchar 值“134a”转换为数据类型 int 时转换失败。

有办法解决这个问题吗？

原文

Here's an example query:

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

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?:

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

Is there a way around this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半岛未凉 2025-01-09 20:42:56

WHERE 子句首先执行。尝试：

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

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

回复收藏 0 原文

~没有更多了~