作者: 左其盛 发表于 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
评论: 0 查看评论 发表评论
百度期待您的加盟
最新新闻:
· 微软不为外界所知的十件趣事(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背后的那些软件
网站导航:博客园首页 个人主页 新闻 闪存 小组 博问 社区 知识库
分享到:
相关推荐
代码如下: create ...剩余的为数字部分 END RETURN CONVERT(bigint,@No) END 此函数可以返回某字符串中的数字部分 PATINDEX函数 返回所查内容在字符串中第一次出现的索引 STUFF函数 删除指定长度的字符并在指定的
该函数是将传进的ip地址转换成bigint类型的sql函数 如:12.3.1.33 -> 201523489
继SQl -Function创建函数数据库输出的结果用逗号隔开,在开发中也有许多以参数的形式传入带逗号字条串参数(数据大时不建议这样做) 例:查找姓名为“张三,李二” 的数据此时在数据库里就要对此参数做处理如图: ...
很齐全的SQL语句,字符串转换语句大全,希望对大家有帮助。
ipv4或ipv6字符串形式的地址转换为BigInteger数字。
JSON.parse 的替代品,它将足够长的整数转换为字符串,从而在 Javascript 中失去精度。 基于 。 用法 var JSONBigInt = require ( 'json-bigint-string' ) ; // console.log(JSON.parse(someJson)); console . ...
用C++编写bigint类型,实现超过float容量的大数字乘法
返回二进制值N的一个字符串表示,在此N是一个长整数(BIGINT)数字,这等价于CONV(N,10,2).如果N是NULL,返回NULL. 4,Bin mysql> select BIN(12); -> '1100'OCT(N) 返回八进制值N的一个字符串的表示,在此N是一...
使用 CAST 函数将 STRING 转为 BIGINT: SELECT CAST(‘00321’ AS BIGINT) FROM table; As a BIGINT it will show on the screen and in delimited text files as 321. 参考:Hive – Converting a string to ...
有一个需求是要在一个云监控的状态值中存储多个状态(包括可同时存在的各种异常、警告状态)使用了位运算机制在一个int型中存储。 现在监控日志数据量非常大(亿级别)需要对数据按每小时、每天进行聚合,供在线报表...
返回二进制值N的一个字符串表示,在此N是一个长整数(BIGINT)数字,这等价于CONV(N,10,2).如果N是NULL,返回NULL. 4,Bin mysql> select BIN(12); -> '1100'OCT(N) 返回八进制值N的一个字符串的表示,在此N是一...
---between and 表示在那个两个数字之间 select * from users where Id between @startId and @endId go exec proc_find_users 42, 64; ----------------------------------------------------------------------...
总结JavaScript中BigIn函数常见的属性 ... 创建 bigint 的方式有两种:在一个整数字面量后面加 n 或者调用 BigInt 函数,该函数从字符串、数字等中生成 bigint。 const bigint = 123456789012345678901234567890123
bigint 大数计算源码
json-source-map 解析/字符串化JSON,并为所有节点的JSON指针提供源映射。 新增:支持BigInt,地图,集合和类型数组。安装npm install json-source-map可能的用例源图当编译为JavaScript的特定于域的语言使用JSON...
ft_bigID(生成19位的Long Integer类型的数据),生成唯一的随机码。
flink-sql-hdfs-connector支持根据数据的事件时间落到对应的分区目录分支说明master分支不放代码,分支对应适应相同版本的flink,例如分支flink-1.10就仅在flink 1.10版本上测试通过使用方法下载代码编译cd flink-...
在JSON api中将可能不属于IEEE 754整数精度的值序列化为字符串是一个好主意,但是例如, { "value" : 9223372036854775807}仍然是有效的RFC4627 JSON字符串,并且在大多数JS运行时中,结果JSON.parse的对象是此对象...
建表的一部分贴出来如下: CREATE TABLE `tb_spec_param` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `cid` bigint(20) NOT NULL COMMENT '商品分类id', `group_id` bigint(20) NOT NULL, `...