SQL Server 验证身份证合法性函数（使用VBScript.RegExp）
SQL Server 验证身份证合法性函数（使用VBScript.RegExp）
用户1148526
发布于 2019-05-25 19:48:19
发布于 2019-05-25 19:48:19
版权声明：本文为博主原创文章，未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1433253
-- 建立正则表达式测试函数
CREATE FUNCTION dbo.RegExpTest
(
@source VARCHAR(5000), --需要匹配的源字符串
@regexp VARCHAR(1000), --正则表达式
@ignorecase BIT = 0 --是否区分大小写，默认为false
)
RETURNS BIT --返回结果0-false，1-true
AS BEGIN
--0（成功）或非零数字（失败），是由 OLE 自动化对象返回的 HRESULT 的整数值。
DECLARE @hr INTEGER
--用于保存返回的对象令牌，以便之后对该对象进行操作
DECLARE @objRegExp INTEGER
DECLARE @objMatches INTEGER
--保存结果
DECLARE @results BIT
/*
创建 OLE 对象实例,只有 sysadmin 固定服务器角色的成员才能执行 sp_OACreate,并确定机器中有VBScript.RegExp类库
*/
EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
/*
以下三个分别是设置新建对象的三个属性。下面是'VBScript.RegExp'中常用的属性举例：
Dim regEx,Match,Matches '建立变量。
Set regEx = New RegExp '建立一般表达式。
regEx.Pattern= patrn '设置模式。
regEx.IgnoreCase = True '设置是否区分大小写。
regEx.Global=True '设置全局可用性。
set Matches=regEx.Execute(string) '重复匹配集合
RegExpTest = regEx.Execute(strng) '执行搜索。
for each match in matches '重复匹配集合
RetStr=RetStr &"Match found at position "
RetStr=RetStr&Match.FirstIndex&".Match Value is '"
RetStr=RetStr&Match.Value&"'."&vbCRLF Next
RegExpTest=RetStr
*/
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
--调用对象方法
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
--释放已创建的 OLE 对象
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END
-- 建立身份证验证函数
ALTER FUNCTION dbo.fn_checkidcard ( @p_idcard VARCHAR(18) )
RETURNS INT
AS BEGIN
DECLARE @v_regstr VARCHAR(2000) ;
DECLARE @v_sum INT ;
DECLARE @v_mod INT ;
DECLARE @v_checkcode CHAR(11) ;
SET @v_checkcode = '10X98765432' ;
DECLARE @v_checkbit CHAR(1) ;
DECLARE @v_areacode VARCHAR(2000) ;
SET @v_areacode = '11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,82,91,' ;
DECLARE @l INT ;
SET @l = LEN(@p_idcard) ;
DECLARE @r INT ;
SET @r = 0 ;
IF @l = 15
BEGIN
IF CHARINDEX(SUBSTRING(@p_idcard, 1, 2) + ',', @v_areacode) = 0
RETURN 0 ;
IF ( ( CAST(( SUBSTRING(@p_idcard, 6, 2) ) AS INT) + 1900 ) % 400 = 0 )
OR ( ( ( CAST(( SUBSTRING(@p_idcard, 6, 2) ) AS INT) + 1900 )
% 100 <> 0 )
AND ( ( CAST(( SUBSTRING(@p_idcard, 6, 2) ) AS INT)
+ 1900 ) % 4 = 0 )
)
SET @v_regstr = '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}$' ;
ELSE
SET @v_regstr = '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$' ;
IF dbo.RegExpTest(@p_idcard, @v_regstr, 0) = 1
SET @r = 1 ;
ELSE
SET @r = 0 ;
END ;
ELSE
IF @l = 18
BEGIN
IF CHARINDEX(SUBSTRING(@p_idcard, 1, 2) + ',', @v_areacode) = 0
RETURN 0 ;
IF ( ( CAST(( SUBSTRING(@p_idcard, 6, 4) ) AS INT) + 1900 )
% 400 = 0 )
OR ( ( ( CAST(( SUBSTRING(@p_idcard, 6, 4) ) AS INT)
+ 1900 ) % 100 <> 0 )
AND ( ( CAST(( SUBSTRING(@p_idcard, 6, 4) ) AS INT)
+ 1900 ) % 4 = 0 )
)
SET @v_regstr = '^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9Xx]$' ;
ELSE
SET @v_regstr = '^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$' ;
IF dbo.RegExpTest(@p_idcard, @v_regstr, 0) = 1
BEGIN
SET @v_sum = ( CAST(SUBSTRING(@p_idcard, 1, 1) AS INT)
+ CAST(SUBSTRING(@p_idcard, 11, 1) AS INT) )
* 7 + ( CAST(SUBSTRING(@p_idcard, 2, 1) AS INT)
+ CAST(SUBSTRING(@p_idcard, 12, 1) AS INT) )
* 9 + ( CAST(SUBSTRING(@p_idcard, 3, 1) AS INT)
+ CAST(SUBSTRING(@p_idcard, 13, 1) AS INT) )
* 10 + ( CAST(SUBSTRING(@p_idcard, 4, 1) AS INT)
+ CAST(SUBSTRING(@p_idcard, 14, 1) AS INT) )
* 5 + ( CAST(SUBSTRING(@p_idcard, 5, 1) AS INT)
+ CAST(SUBSTRING(@p_idcard, 15, 1) AS INT) )
* 8 + ( CAST(SUBSTRING(@p_idcard, 6, 1) AS INT)
+ CAST(SUBSTRING(@p_idcard, 16, 1) AS INT) )
* 4 + ( CAST(SUBSTRING(@p_idcard, 7, 1) AS INT)
+ CAST(SUBSTRING(@p_idcard, 17, 1) AS INT) )
* 2 + CAST(SUBSTRING(@p_idcard, 8, 1) AS INT) * 1
+ CAST(SUBSTRING(@p_idcard, 9, 1) AS INT) * 6
+ CAST(SUBSTRING(@p_idcard, 10, 1) AS INT) * 3 ;
SET @v_mod = @v_sum % 11 ;
SET @v_checkbit = SUBSTRING(@v_checkcode, @v_mod + 1,
1) ;
IF @v_checkbit = SUBSTRING(@p_idcard, 18, 1)
SET @r = 1 ;
ELSE
SET @r = 0 ;
END ;
ELSE
RETURN 0 ;
END ;
RETURN @r ;
-- 身份证号码位数不对
END ;
;
-- 开启Ole Automation Procedures配置
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
-- 测试
SELECT dbo.fn_checkidcard('110102197203270816'); 问题：这种方法对每一行都要创建对象、设置对象属性、释放对象等一系列操作，数据量大时性能很差。但这种在SQL Server中使用正则表达式的方法比较简单。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2016年12月27日，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。