SQL Server 的 ISNUMERIC 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32135878/
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
SQL Server's ISNUMERIC function
提问by weeraa
I need to checking a column where numeric or not in SQL Server 2012.
我需要检查 SQL Server 2012 中是否为数字的列。
This my case code.
这是我的案例代码。
CASE
WHEN ISNUMERIC(CUST_TELE) = 1
THEN CUST_TELE
ELSE NULL
END AS CUSTOMER_CONTACT_NO
But when the '78603D99'value is reached, it returns 1which means SQL Server considered this string as numeric.
但是当'78603D99'达到该值时，它会返回1，这意味着 SQL Server 将此字符串视为数字。
Why is that?
这是为什么？
How to avoid this kind of issues?
如何避免此类问题？
回答by Paul Spangle
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.
不幸的是，ISNUMERIC()SQL Server 中的函数有很多怪癖。它并不完全是错误的，但它很少能达到人们第一次使用它时的预期。
However, since you're using SQL Server 2012 you can use the TRY_PARSE()function which will do what you want.
但是，由于您使用的是 SQL Server 2012，您可以使用该TRY_PARSE()函数来完成您想要的操作。
This returns NULL:
SELECT TRY_PARSE('7860D399' AS int)
这将返回 NULL：
SELECT TRY_PARSE('7860D399' AS int)
This returns 7860399
SELECT TRY_PARSE('7860399' AS int)
这将返回 7860399
SELECT TRY_PARSE('7860399' AS int)
https://msdn.microsoft.com/en-us/library/hh213126.aspx
https://msdn.microsoft.com/en-us/library/hh213126.aspx
Obviously, this works for datatypes other than INTas well. You say you want to check that a value is numeric, but I think you mean INT.
显然，这适用于其他数据类型INT。你说你想检查一个值是否是数字，但我认为你的意思是INT.
回答by Gordon Linoff
Although try_convert()or try_parse()works for a built-in type, it might not do exactly what you want. For instance, it might allow decimal points, negative signs, and limit the length of digits.
尽管try_convert()或try_parse()适用于内置类型，但它可能无法完全满足您的要求。例如，它可能允许小数点、负号并限制数字长度。
Also, isnumeric()is going to recognize negative numbers, decimals, and exponential notation.
此外，isnumeric()将识别负数、小数和指数表示法。
If you want to test a string only for digits, then you can use not likelogic:
如果您只想测试数字的字符串，则可以使用not like逻辑：
(CASE WHEN CUST_TELE NOT LIKE '%[^0-9]%'
THEN CUST_TELE
END) AS CUSTOMER_CONTACT_NO
This simply says that CUST_TELEcontains no characters that are not digits.
这只是说不CUST_TELE包含不是数字的字符。
回答by screechOwl
Nothing substantive to add but a couple warnings.
除了一些警告之外，没有什么实质性的补充。
1) ISNUMERIC() won't catch blanks but they will break numeric conversions.
1) ISNUMERIC() 不会捕获空格，但会破坏数字转换。
2) If there is a single non-numeric character in the field and you use REPLACE to get rid of it you still need to handle the blank (usually with a CASE statement).
2) 如果字段中有单个非数字字符，并且您使用 REPLACE 来摆脱它，您仍然需要处理空白（通常使用 CASE 语句）。
For instance if the field contains a single '-' character and you use this:
例如，如果该字段包含单个“-”字符并且您使用此：
cast(REPLACE(myField, '-', '') as decimal(20,4)) myNumField
it will fail and you'll need to use something like this:
它会失败，你需要使用这样的东西：
CASE WHEN myField IN ('','-') THEN NULL ELSE cast(REPLACE(myField, '-', '') as decimal(20,4)) END myNumField
