检查字段是否为数字，然后只在一个语句中的那些字段上执行比较？ [英] Check if field is numeric, then execute comparison on only those field in one statement?

问题描述

这可能很简单，但我不是SQL whiz，所以我迷路了。我理解sql接受你的查询，并以一定的顺序执行它，我相信是为什么这个查询不工作：

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:

select * from purchaseorders
where IsNumeric(purchase_order_number) = 1
and cast(purchase_order_number as int) >= 7

purchase_order_number字段中的最大值是数字，但最近我们引入了字母数字。我想获得的数据是看看'7'是否大于最高数字purchase_order_number。

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.

Numeric（）函数过滤掉字母数字字段，但是后续的转换比较会抛出此错误：

The Numeric() function filters out the alphanumeric fields fine, but doing the subsequent cast comparison throws this error:

Conversion failed when converting the nvarchar value '124-4356AB' to data type int.

我不是问什么错误的意思，这很明显。我要问是否有办法在单个查询中完成我想要的，最好是在where子句中由于ORM约束。

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.

推荐答案

这对你有效吗？

select * from purchaseorders
where (case when IsNumeric(purchase_order_number) = 1
       then cast(purchase_order_number as int)
       else 0 end) >= 7

这篇关于检查字段是否为数字，然后只在一个语句中的那些字段上执行比较？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！