`

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

评论: 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背后的那些软件

网站导航:博客园首页  个人主页  新闻  闪存  小组  博问  社区  知识库

分享到:
评论

相关推荐

    T-SQL 语法参考手册.docx

    T-SQL还包含各种内置函数,如数学函数、字符串函数、日期时间函数等,用于处理特定任务。 5. 数据类型:理解数据类型是使用任何编程语言的基础,T-SQL提供了一系列的数据类型来适应不同类型的值,如整数、浮点数、...

    SQL_Server_2008:T-SQL初学者指南

    T-SQL是SQL Server特有的SQL方言,它为数据库管理员和开发人员提供了一种强大的工具来管理和操作数据。在本章节中,我们将深入探讨T-SQL的基本对象,包括但不限于常量、标识符、分隔符等。 **常量**(Literal ...

    T_SQL语言(PPT)

    T-SQL,全称为Transact-SQL,是由Microsoft公司为SQL Server数据库管理系统设计的一种扩展的SQL语言。它不仅包含了标准SQL的功能,还添加了许多特有特性,以增强数据库管理和应用程序开发的灵活性和效率。 **T-SQL...

    用sql获取某字符串中的数字部分的语句

    首先,我们来看标题提到的SQL语句,它创建了一个名为`dbo.F_Get_No`的用户定义函数,这个函数接受一个`varchar(100)`类型的参数`@No`,并返回一个`bigint`类型的值,即字符串中的纯数字部分。 函数的核心逻辑在于`...

    T-SQL中的数据类型

    在T-SQL中,数据类型是定义数据结构和表达式的基础,它们决定了数据的存储方式、占用的空间以及处理数据的规则。SQL Server 2000 提供了一系列丰富的数据类型,以满足各种不同应用场景的需求。 1. **整数数据类型**...

    T-SQL数据类型.ppt

    **SQL Server 2008 中的 T-SQL 数据类型** 在 SQL Server 2008 中,数据类型是至关重要的概念,它们定义了数据库中存储数据的种类和格式。理解并正确选择数据类型对于创建高效、可靠的数据库至关重要。T-SQL ...

    如何用T-SQL语句建立跟踪

    在IT领域,特别是数据库管理与优化中,使用T-SQL语句进行跟踪是十分关键的技能之一。T-SQL(Transact-SQL)是Microsoft SQL Server使用的SQL方言,它不仅支持标准SQL的功能,还提供了额外的编程功能。通过T-SQL,...

    SQL 字符串转换语句大全

    ### SQL字符串转换语句大全详解 #### 一、概述 在数据库操作中,字符串处理是一项常见且重要的功能。SQL提供了丰富的内置函数来帮助用户完成字符串的格式化与转换任务,这些函数可以极大地提高数据处理效率并简化...

    T_SQL语言(很有帮助的)

    T-SQL提供了丰富的内置函数,如数学函数、字符串函数、日期/时间函数等,用于处理数据计算、字符串操作和日期时间转换等任务。此外,还可以创建自定义函数以满足特定业务需求。 总结来说,T-SQL是SQL Server中的...

    SQL基础T-SQL数据类型[参考].pdf

    在T-SQL中,数据类型是定义变量、常量、函数参数和表列的基础,它们决定了数据如何被存储和处理。 1. 数值型数据类型: - INTEGER (INT):4字节,存储范围-2,147,483,648到2,147,483,647。 - SMALLINT:2字节,...

    ipv4和ipv6字符串转换成数字

    根据给定的信息,本文将详细解释如何将IPv4与IPv6地址从字符串形式转换为`BigInteger`数字形式,以及反向操作的过程。 ### IPv4与IPv6简介 #### IPv4 IPv4(Internet Protocol Version 4)是互联网协议的第四版,...

    实验三 用T-SQL创建与修改表的操作

    ### T-SQL创建与修改表的操作实验解析 #### 实验背景 本次实验旨在通过实际操作加深对T-SQL的理解,并熟练掌握使用T-SQL进行表的创建与修改的方法。实验内容涉及了创建数据库、创建表、添加约束以及修改表结构等...

    mysql的字符串函数

    BIN(N) 函数返回二进制值 N 的一个字符串表示,在此 N 是一个长整数(BIGINT)数字,这等价于 CONV(N,10,2)。如果 N 是 NULL,返回 NULL。 mysql> select BIN(12); -> '1100' 5. OCT(N) 函数 OCT(N) 函数返回八进制...

    SQL Server数据库中创建MD5函数过程

    创建自定义MD5函数的过程通常涉及编写T-SQL代码,利用内置的转换和字符串处理函数。在SQL Server中,可以使用`HASHBYTES`函数来计算哈希值,但默认不支持MD5算法,所以我们需要通过转换方式实现。 以下是创建自定义...

    SQL Server_2008入门培训初学者指南

    对于初学者来说,掌握 **T-SQL** (Transact-SQL) 是必不可少的一步。T-SQL 是 SQL Server 的专有扩展语言,用于查询、更新和管理 SQL Server 数据库。本文将详细介绍 SQL Server 2008 中 T-SQL 的基础知识。 ### T-...

    T-SQL的数据类型.pptx

    在SQL Server中,T-SQL(Transact-SQL)是用于管理和操作数据库的语言,它包含了丰富的数据类型,用于表示各种不同类型的数据。以下是T-SQL中常见的数据类型及其详细说明: 1. **数值类型**: - **Int**: 4个字节...

    flink-sql-demo-data-part2.tar.gz

    首先,我们关注到这个压缩包的名字“flink-sql-demo-data-part2.tar.gz”,这暗示了它是Flink SQL演示项目的一部分,可能用于展示或测试特定的SQL查询功能。通常,这样的数据集会包含模拟真实业务场景的数据,帮助...

    SQL常用函数总结

    在实际应用中,我们经常需要连接两个字符串。SQL Server、Oracle、DB2和IQ都支持连接字符串的语句。例如,在SQL Server中,我们可以使用以下语句连接两个字符串: ``` Select 'Hello' + 'Toone' ``` 空值转换 在...

    sqlserver-oracle 数据类型对照

    - `STR`和`CONVERT`在SQL Server中实现数字到字符串的转换。 4. **其他行级别的函数**: - `DECODE`在Oracle中用于条件判断,SQL Server中可使用`CASE`或`IIF`表达式替代。 - `COALESCE`在Oracle和SQL Server中...

    达梦sql语言使用手册

    ### 达梦SQL语言使用手册知识点详述 #### 第1章 结构化查询语言DM_SQL简介 ##### 1.1 DM_SQL语言的特点 DM_SQL(达梦SQL)是达梦数据库管理系统提供的一种标准的SQL语言实现,具有以下特点: 1. **兼容性**:高度...

Global site tag (gtag.js) - Google Analytics