T-SQL determines whether a string is a bigint function (the full-angle number must be regarded as unqualified)

Recently, a project has encountered the following problem: You need to convert a string card number to a bigint card number. The built-in isnumeric function of T-SQL cannot be used. It considers that the qualified number is not necessarily a bigint, such as a number with a decimal point or a number with a scientific count. I searched online and found no help in Chinese documents. I found someone on sqlservercentral wrote this function. Key Algorithm It is a charindex + substring loop, one by one to see whether there are illegal characters. Article Some people in the comments said they could use the patindex function, which is faster. However, both of them cannot solve the problem of full-angle numbers. They both think that full-angle numbers are valid numbers. Of course, an error will be reported when the number is actually converted to bigint.

I searched the internet and noticed the keyword collate. The general explanation is that it can specify sorting rules. The rules that can be changed include uppercase and lowercase letters, accents, Kana (only available in Japanese), and full-width halfwidth. This keyword is rarely used in Chinese systems. Generally, the default case sensitivity is used. The collate keyword must be used to differentiate all corners. It can be used as follows: charindex (substring (@ s, @ I, 1), '000000' collate chinese_prc_cs_as_ks_ws), in which the chinese_prc parameter after collate specifies the character set used Code Page (in fact, the language used), followed by up to four × S, S represents sensitive, corresponding I represents not sensitive. For example, chinese_prc_cs_as_ks_ws indicates Simplified Chinese, case sensitive (CS), stress sensitive (AS, Which is meaningless to Chinese), and distinguish the Kana type (KS, Which is meaningless to Chinese ), KS and chinese_prc_ci_ai are simplified Chinese characters. They are case insensitive and stress insensitive. They are not Kana-type or full-width. If the last two parameters are ignored, the request is denied. Of course, binary sorting can also be directly specified, and the problem of full-width half-width is solved naturally, and binary sorting is still faster: charindex (substring (@ s, @ I, 1 ), '123' collate chinese_prc_bin)

Therefore, the Core Algorithm for determining whether a string is bigint in theory has four solutions:

Charindex (substring (@ s, @ I, 1), '000000' collate chinese_prc_cs_as_ks_ws)
Charindex (substring (@ s, @ I, 1), '66661' collate chinese_prc_bin)
Patindex ('% [^ 0-9] %', @ s collate chinese_prc_cs_as_ks_ws)
Patindex ('% [^ 0-9] %', @ s collate chinese_prc_bin)

However, the experiment found that the third method cannot solve the problem and still considers the full-angle number as a valid number. Looking at Microsoft's msdn documentation, no answers are found on the Internet. You can use either of the other three methods. Theoretically, the last one is the fastest.

The complete function code is as follows:

/*
-- Tests pass isnumeric and fail isbigint and fail cast (VC as bigint)

-- Range
Select isnumeric ('-9223372036854775809'), DBO. isbigint ('-9223372036854775809 ')
Select isnumeric ('000000'), DBO. isbigint ('000000 ')

-- Invalid chars
Select isnumeric ('-5d2'), DBO. isbigint ('-5d2 ')
Select isnumeric ('-5e2'), DBO. isbigint ('-5e2 ')
Select isnumeric ('+ 3,4'), DBO. isbigint ('+ 3,4 ')
Select isnumeric ('+ 100'), DBO. isbigint (' + 100 ')

-- Pass this strange case
Select isnumeric ('000000'), DBO. isbigint ('000000 ')
*/

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 (@ ))

-- 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), '000000' collate chinese_prc_ci_as_ws) = 0 set @ valid = 0
-- If charindex (substring (@ s, @ I, 1), '000000' 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> '000000' set @ valid = 0
If @ isneg = 0 and @ s> '000000' set @ valid = 0
End

Return @ valid
End
Go