SQL Server 的 ISNUMERIC 函数 [英] SQL Server's ISNUMERIC function

问题描述

我需要检查 SQL Server 2012 中是否为数字的列.

I need to checking a column where numeric or not in SQL Server 2012.

这是我的案例代码.

CASE
    WHEN ISNUMERIC(CUST_TELE) = 1 
      THEN CUST_TELE 
      ELSE NULL 
END AS CUSTOMER_CONTACT_NO

但是当达到 '78603D99' 值时，它返回 1 这意味着 SQL Server 将此字符串视为数字.

But when the '78603D99' value is reached, it returns 1 which means SQL Server considered this string as numeric.

这是为什么?

如何避免此类问题?

推荐答案

不幸的是，SQL Server 中的 ISNUMERIC() 函数有很多怪癖.它并不完全是错误的，但它很少能达到人们第一次使用它时的预期.

Unfortunately, the ISNUMERIC() function in SQL Server has many quirks. It's not exactly buggy, but it rarely does what people expect it to when they first use it.

但是，由于您使用的是 SQL Server 2012，您可以使用 TRY_PARSE() 函数来完成您想要的操作.

However, since you're using SQL Server 2012 you can use the TRY_PARSE() function which will do what you want.

这将返回 NULL:SELECT TRY_PARSE('7860D399' AS int)

返回 7860399SELECT TRY_PARSE('7860399' AS int)

https://msdn.microsoft.com/en-us/library/hh213126.aspx

显然，这也适用于 INT 以外的数据类型.你说你想检查一个值是否是数字，但我认为你的意思是 INT.

Obviously, this works for datatypes other than INT as well. You say you want to check that a value is numeric, but I think you mean INT.

这篇关于SQL Server 的 ISNUMERIC 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！