t-sql判断一个字符串是否为bigint的函数（全角数字需要判断为不合格）
作者: 左其盛 发表于 2010-07-18 22:30 原文链接 阅读: 131 评论: 0
最近在做的一个项目遇到这么一个问题：需要把一个字符串格式的卡号转换为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
最新新闻：
· 微软不为外界所知的十件趣事(2010-07-18 22:58)
· 中国第2季搜索引擎市场规模达26亿 百度破70%(2010-07-18 22:54)
· Facebook用户数下周达5亿 邀请用户共享故事(2010-07-18 22:49)
· 开源的可视化编辑器 KindEditor 3.5.1 发布(2010-07-18 22:35)
· WordPress 陷入开源‘边界’之争(2010-07-18 17:47)
编辑推荐：揭秘Facebook背后的那些软件
