SQL Server ISNUMERIC() 澄清
我有一个表 X，里面有一个 accountNo 的列表.该字段(accoutNo) 是一个nvarchar(8).现在的问题是有时我们也会在该字段中获取字符，我想将其转换为 bigint.
I have a table X with a list of accountNo's. This field(accoutNo) is a nvarchar(8). Now issue here is sometimes we get characters in this field as well and I want to convert it into bigint.
抱歉，我无法将其以表格形式放在这里.
Sorry I'am not able to put this in a table format here.
我可以检查 accountNo 是否是数值:
I'am able to check if a accountNo is a numeric value or not:
select x.accountNo from x where ISNUMERIC(x.accountNo)=1
但是当我尝试仅在 accountNo 是数字时才转换值时，我仍然不能，我很困惑:
but when I try to only convert the values if an accountNo is numeric, I still can't, I'm confused:
select x.accountNo, convert(bigint,x.accountNo) from x where ISNUMERIC(x.accountNo)=1
我收到的具体错误:
消息 8114，级别 16，状态 5，第 1 行错误转换数据类型nvarchar 到 bigint.
Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.
示例数据
accountNo A0001001 A0001002 A0001003 /0005856 !0005046 ~0005872 A.005698 A/005623 A./00578 ./214536
推荐答案
你应该使用 CAST() 或 TRY_CAST() 代替:
You should use CAST() or TRY_CAST() instead:
declare @test nvarchar(8) = '12345678'
select cast(@test as bigint) -- errors on failure
select try_cast(@test as bigint) -- returns null on failure
另外，重要的是要指出 ISNUMERIC() 并不完美.来自文档:
Also, important to point out the ISNUMERIC() isn't perfect. From the docs:
ISNUMERIC 对某些不是数字的字符返回 1，例如加号 (+)、减号 (-) 和有效的货币符号，例如美元符号 ($).有关货币符号的完整列表，请参阅 money 和 smallmoney (Transact-SQL).
ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney (Transact-SQL).
出于这个原因，我认为逻辑检查在这里没有价值.最好对所有值使用 TRY_CAST()，无论是否存在字符，并以可预测的方式处理空响应.
For this reason I don't think the logical check is of value here. Best to use TRY_CAST() on all values, regardless of the presence of characters and handle the null response in a predictable manner.
相关文章