tsql - SQL无效转换返回null而不是抛出错误

Question

我有一个带有 varchar 列的表，我想找到与某个数字匹配的值。因此，假设该列包含以下条目(现实生活中的数百万行除外):

123456789012
2345678
3456
23 45
713?2
00123456789012

所以我决定我想要所有数字为 123456789012 的行写一个看起来像这样的语句:

SELECT * FROM MyTable WHERE CAST(MyColumn as bigint) = 123456789012

它应该返回第一行和最后一行，但整个查询会失败，因为它无法将“23 45”和“713?2”转换为 bigint。

是否有另一种方法可以进行转换，为无法转换的值返回 NULL？

Answer 1

SQL Server 不保证 bool 运算符短路，请参阅 On SQL Server boolean operator short-circuit .所以所有解决方案都使用 ISNUMERIC(...) AND CAST(...)从根本上是有缺陷的(它们可能会起作用，但是以后可能会根据生成的计划任意失败)。更好的解决方案是使用 CASE，正如 Thomas 建议的那样:CASE ISNUMERIC(...) WHEN 1 THEN CAST(...) ELSE NULL END .但是，正如 gbn 所指出的，ISNUMERIC众所周知，在确定“数字”的含义时非常挑剔，而且在许多情况下，人们希望它返回 0，它返回 1。因此将 CASE 与 LIKE 混合:

CASE WHEN MyRow NOT LIKE '%[^0-9]%' THEN CAST(MyRow as bigint) ELSE NULL END

但真正的问题是，如果您有数百万行并且必须像这样搜索它们，您将始终以端到端的方式扫描，因为该表达式不支持 SARG(无论我们如何重写它)。这里的真正问题是数据纯度，应该在填充数据的适当级别解决。要考虑的另一件事是是否可以使用此表达式创建持久计算列并在其上创建过滤索引以消除 NULL(即非数字)。那会加快一些事情。