SQL 检查字段是否为数字，然后在一个语句中仅对这些字段执行比较？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13420508/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if field is numeric, then execute comparison on only those field in one statement?
提问by ledgeJumper
This may be simple, but I am no SQL whiz so I am getting lost. I understand that sql takes your query and executes it in a certain order, which I believe is why this query does not work:
这可能很简单，但我不是 SQL 高手，所以我迷路了。我知道 sql 接受您的查询并按特定顺序执行它，我认为这就是此查询不起作用的原因：
select * from purchaseorders
where IsNumeric(purchase_order_number) = 1
and cast(purchase_order_number as int) >= 7
MOST of the purchar_order_number fields are numeric, but we introduce alphanumeric ones recently. The data I am trying to get is to see if '7' is greater than the highest numeric purchase_order_number.
大多数 purchar_order_number 字段是数字字段，但我们最近引入了字母数字字段。我试图获得的数据是查看“7”是否大于最高数字采购订单编号。
The Numeric() function filters out the alphanumeric fields fine, but doing the subsequent cast comparison throws this error:
Numeric() 函数可以很好地过滤掉字母数字字段，但是进行后续的转换比较会引发此错误：
Conversion failed when converting the nvarchar value '124-4356AB' to data type int.
I am not asking what the error means, that is obvious. I am asking if there is a way to accomplish what I want in a single query, preferably in the where clause due to ORM constraints.
我不是在问错误是什么意思，这很明显。我在问是否有一种方法可以在单个查询中完成我想要的操作，由于 ORM 约束，最好在 where 子句中完成。
回答by fnurglewitz
does this work for you?
这对你有用吗？
select * from purchaseorders
where (case when IsNumeric(purchase_order_number) = 1
then cast(purchase_order_number as int)
else 0 end) >= 7
回答by ShyJ
You can do a select with a subselect
您可以使用子选择进行选择
select * from (
select * from purchaseorders
where IsNumeric(purchase_order_number) = 1) as correct_orders
where cast(purchase_order_number as int) >= 7
回答by Robinson
try this:
尝试这个：
select * from purchaseorders
where try_cast(purchase_order_number as int) >= 7
