問題描述
我有一個表 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模板網！