t-sql判斷一個字串是否為bigint的函數（全形數字需要判斷為不合格）

最近在做的一個項目遇到這麼一個問題：需要把一個字串格式的卡號轉換為bigint格式的卡號。t-sql內建的isnumeric函數不能用。它認為合格的數字不一定是bigint，比如一些帶小數點的數字，科學計數的數字。上網搜，中文資料中沒發現有協助的，在sqlservercentral上發現有人寫過這個函數了。關鍵的演算法就是charindex + substring迴圈，一個一個看有沒有不合法的字元。文章的評論中有人說可以用patindex函數，更快。不過用了這兩個都解決不了全形數位問題，他們都認為全形數字是合法的數字，當然實際轉換為bigint的時候會報錯。
 
 又上網搜了搜，注意到了COLLATE關鍵字。一般的解釋是它可以指定定序。可以改變的規則有大小寫、重音、假名（日語才有）、全形半形。中文系統中很少用到這個關鍵字。一般就用預設的大小寫不敏感。我這裡想區分全形半形，必須用COLLATE關鍵字。可以這麼用：charindex(substring(@s, @i, 1), '0123456789' COLLATE  Chinese_PRC_CS_AS_KS_WS)，其中COLLATE後面的參數中Chinese_PRC指定字元集所使用的字碼頁（其實就是所用的語言），後面最多可以跟四個×s，S表示敏感，對應的I表示不敏感。比如Chinese_PRC_CS_AS_KS_WS表示是簡體中文，大小寫敏感（CS），重音敏感（AS，這個對漢語沒意義），區分假名類型（KS，這個對漢語也沒意義），區分全形半形（KS），Chinese_PRC_CI_AI表示簡體中文，大小寫不敏感，重音不敏感，不區分假名類型，不區分全形半形。後兩個參數忽略掉就表示否定。當然還可以直接指定二進位排序，全形半形的問題就自然解決了，而且二進位排序還更快一些：charindex(substring(@s, @i, 1), '0123456789' COLLATE  Chinese_PRC_BIN)
 
 因此，理論上這個判斷字串是否為bigint的問題的核心演算法有四種解決方案：
 
 charindex(substring(@s, @i, 1), '0123456789' COLLATE  Chinese_PRC_CS_AS_KS_WS)
 charindex(substring(@s, @i, 1), '0123456789' COLLATE  Chinese_PRC_BIN)
 patindex('%[^0-9]%',@s COLLATE  Chinese_PRC_CS_AS_KS_WS )
 patindex('%[^0-9]%',@s COLLATE  Chinese_PRC_BIN )
 
 不過實驗發現第三種不能解決問題，仍然認為全形數字是合法的數字。看微軟msdn文檔，上網搜都沒有找到答案。其他三種都可以。理論上最後一種最快。
 
 下面是完整的函數的代碼：

/*
-- Tests pass isnumeric AND fail IsBigInt AND fail cast(vc as bigint)

-- range
SELECT IsNumeric('-9223372036854775809'), dbo.IsBigInt('-9223372036854775809')
SELECT IsNumeric('9223372036854775808'), dbo.IsBigInt('9223372036854775808')

-- invalid chars
SELECT IsNumeric('-5d2'), dbo.IsBigInt('-5d2')
SELECT IsNumeric('-5e2'), dbo.IsBigInt('-5e2')
SELECT IsNumeric('+3,4'), dbo.IsBigInt('+3,4')
SELECT IsNumeric('+3.4'), dbo.IsBigInt('+3.4')

-- pass this strange case
SELECT IsNumeric('00000000000000000000000000001'), dbo.IsBigInt('00000000000000000000000000001')
*/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IsBigInt') AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.IsBigInt
GO

CREATE FUNCTION dbo.IsBigInt (@a varchar(30))
returns bit
AS
BEGIN
 -- Submitted to SqlServerCentral by William Talada
 DECLARE
  @s varchar(30),
  @i int,
  @IsNeg bit,
  @valid int

 -- assume the best
 SET @valid = 1
 SET @IsNeg=0
 SET @s = ltrim(rtrim(@a))

 -- strip OFF negative sign
 IF len(@s) > 0
 AND LEFT(@s, 1) = '-'
 BEGIN
  SET @IsNeg=1
  SET @s = RIGHT(@s, len(@s) - 1)
 END

 -- strip OFF positive sign
 IF len(@s) > 0
 AND LEFT(@s, 1) = '+'
 BEGIN
  SET @s = RIGHT(@a, len(@a) - 1)
 END

 -- strip leading zeros
 while len(@s) > 1 and left(@s,1) = '0'
  set @s = right(@s, len(@s) - 1)

 -- 19 digits max
 IF len(@s) > 19 SET @valid = 0

 -- the rest must be numbers only
 --SET @i = len(@s)

 --WHILE @i >= 1
 --BEGIN
 ----IF charindex(substring(@s, @i, 1), '0123456789' COLLATE  Chinese_PRC_CI_AS_WS ) = 0 SET @valid = 0
 -- IF charindex(substring(@s, @i, 1), '0123456789' COLLATE  Chinese_PRC_BIN ) = 0 SET @valid = 0

 -- SET @i = @i - 1
 --END
 
 --if patindex('%[^0-9]%',@s COLLATE  Chinese_PRC_CI_AS_WS )>0
 if patindex('%[^0-9]%',@s COLLATE  Chinese_PRC_BIN )>0
  set @valid=0

 -- check range
 IF @valid = 1 AND len(@s) = 19
 BEGIN
  IF @isNeg = 1 AND @s > '9223372036854775808' SET @valid = 0
  IF @IsNeg = 0 AND @s > '9223372036854775807' SET @valid = 0
 END

 RETURN @valid
END
go