問(wèn)題描述
我有一個(gè)表 X，里面有一個(gè) accountNo 的列表.該字段(accoutNo) 是一個(gè)nvarchar(8).現(xiàn)在的問(wèn)題是有時(shí)我們也會(huì)在該字段中獲取字符，我想將其轉(zhuǎn)換為 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.
抱歉，我無(wú)法將其以表格形式放在這里.
Sorry I'am not able to put this in a table format here.
我可以檢查 accountNo 是否是數(shù)值:
I'am able to check if a accountNo is a numeric value or not:
select x.accountNo from x where ISNUMERIC(x.accountNo)=1
但是當(dāng)我嘗試僅在 accountNo 是數(shù)字時(shí)才轉(zhuǎn)換值時(shí)，我仍然不能，我很困惑:
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
我收到的具體錯(cuò)誤:
消息 8114，級(jí)別 16，狀態(tài) 5，第 1 行錯(cuò)誤轉(zhuǎn)換數(shù)據(jù)類型nvarchar 到 bigint.
Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.
示例數(shù)據(jù)
accountNo A0001001 A0001002 A0001003 /0005856 !0005046 ~0005872 A.005698 A/005623 A./00578 ./214536
推薦答案
你應(yīng)該使用 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() 并不完美.來(lái)自文檔:
Also, important to point out the ISNUMERIC() isn't perfect. From the docs:
ISNUMERIC 對(duì)某些不是數(shù)字的字符返回 1，例如加號(hào) (+)、減號(hào) (-) 和有效的貨幣符號(hào)，例如美元符號(hào) ($).有關(guān)貨幣符號(hào)的完整列表，請(qǐng)參閱 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).
出于這個(gè)原因，我認(rèn)為邏輯檢查在這里沒(méi)有價(jià)值.最好對(duì)所有值使用 TRY_CAST()，無(wú)論是否存在字符，并以可預(yù)測(cè)的方式處理空響應(yīng).
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.
這篇關(guān)于SQL Server ISNUMERIC() 澄清的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！