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.

这篇关于SQL Server ISNUMERIC() 澄清的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！