1、 简单的检验 ,不校验校验位:
create
FUNCTION
[
fn_ValidateIDC
]
(
@idc varchar ( 18 )
) RETURNS BIT
AS
begin
if ( isnumeric ( @idc ) = 1 and len ( @idc ) = 15 and isdate ( substring ( @idc , 7 , 6 )) = 1
and ( right ( @idc , 1 ) = ' 0 ' or right ( @idc , 1 ) = ' 1 ' ))
or ( len ( @idc ) = 18 and isnumeric ( left ( @idc , 17 )) = 1 and isdate ( substring ( @idc , 7 , 8 )) = 1
and patindex ( ' %[X0-9]% ' , right ( @idc , 1 )) > 0 )
return 1
return 0
end
go
select dbo.fn_ValidateIDC( ' 12345619780901231X ' )
drop function fn_ValidateIDC
(
@idc varchar ( 18 )
) RETURNS BIT
AS
begin
if ( isnumeric ( @idc ) = 1 and len ( @idc ) = 15 and isdate ( substring ( @idc , 7 , 6 )) = 1
and ( right ( @idc , 1 ) = ' 0 ' or right ( @idc , 1 ) = ' 1 ' ))
or ( len ( @idc ) = 18 and isnumeric ( left ( @idc , 17 )) = 1 and isdate ( substring ( @idc , 7 , 8 )) = 1
and patindex ( ' %[X0-9]% ' , right ( @idc , 1 )) > 0 )
return 1
return 0
end
go
select dbo.fn_ValidateIDC( ' 12345619780901231X ' )
drop function fn_ValidateIDC
上面的代码仅检查位数及相应日期位是否合理,不作深内校验,上面的代码 可以简单修改给表的列上做check,网友自行修改。
2、 如果要严格来校验,现我把一个网友(loworth )的代码进行修改整理给大家看:
CREATE
FUNCTION
[
fn_ValidateIDC
]
(
@idc varchar ( 18 )
) RETURNS BIT
AS
BEGIN
DECLARE @validFactors VARCHAR ( 17 ), @validCodes VARCHAR ( 11 ), @i TINYINT , @iTemp INT
SELECT @validFactors = ' 79A584216379A5842 ' , @validCodes = ' 10X98765432 ' , @i = 1 , @iTemp = 0
-- 验证校验位
IF LEN ( @idc ) <> 15 AND LEN ( @idc ) <> 18 -- 身份证号只有15或18位
RETURN ( 0 )
IF LEN ( @idc ) = 15 -- 如果是15位身份证 则只验证日期
IF ( ISDATE ( ' 19 ' + SUBSTRING ( @idc , 7 , 6 )) = 0 and ' 19 ' + SUBSTRING ( @idc , 7 , 6 ) between ' 1900-01-01 ' and ' 2010-01-01 ' )
RETURN ( 0 )
ELSE
RETURN ( 1 )
-- 18位身份证 验证日期 校验位
IF ( ISDATE ( SUBSTRING ( @idc , 7 , 8 )) = 0 and SUBSTRING ( @idc , 7 , 6 ) between ' 1900-01-01 ' and ' 2010-01-01 ' ) -- 验证日期
RETURN ( 0 )
-- -验证校验位开始
WHILE @i < 18
BEGIN
SELECT @iTemp = @iTemp + CAST ( SUBSTRING ( @idc , @i , 1 ) AS INT ) *
( CASE SUBSTRING ( @validFactors , @i , 1 ) WHEN ' A ' THEN 10 ELSE SUBSTRING ( @validFactors , @i , 1 ) END )
, @i = @i + 1
END
IF SUBSTRING ( @validCodes , @iTemp % 11 + 1 , 1 ) = RIGHT ( @idc , 1 )
RETURN 1
ELSE
RETURN 0
RETURN 0
END
GO
(
@idc varchar ( 18 )
) RETURNS BIT
AS
BEGIN
DECLARE @validFactors VARCHAR ( 17 ), @validCodes VARCHAR ( 11 ), @i TINYINT , @iTemp INT
SELECT @validFactors = ' 79A584216379A5842 ' , @validCodes = ' 10X98765432 ' , @i = 1 , @iTemp = 0
-- 验证校验位
IF LEN ( @idc ) <> 15 AND LEN ( @idc ) <> 18 -- 身份证号只有15或18位
RETURN ( 0 )
IF LEN ( @idc ) = 15 -- 如果是15位身份证 则只验证日期
IF ( ISDATE ( ' 19 ' + SUBSTRING ( @idc , 7 , 6 )) = 0 and ' 19 ' + SUBSTRING ( @idc , 7 , 6 ) between ' 1900-01-01 ' and ' 2010-01-01 ' )
RETURN ( 0 )
ELSE
RETURN ( 1 )
-- 18位身份证 验证日期 校验位
IF ( ISDATE ( SUBSTRING ( @idc , 7 , 8 )) = 0 and SUBSTRING ( @idc , 7 , 6 ) between ' 1900-01-01 ' and ' 2010-01-01 ' ) -- 验证日期
RETURN ( 0 )
-- -验证校验位开始
WHILE @i < 18
BEGIN
SELECT @iTemp = @iTemp + CAST ( SUBSTRING ( @idc , @i , 1 ) AS INT ) *
( CASE SUBSTRING ( @validFactors , @i , 1 ) WHEN ' A ' THEN 10 ELSE SUBSTRING ( @validFactors , @i , 1 ) END )
, @i = @i + 1
END
IF SUBSTRING ( @validCodes , @iTemp % 11 + 1 , 1 ) = RIGHT ( @idc , 1 )
RETURN 1
ELSE
RETURN 0
RETURN 0
END
GO
测试:
select
dbo.fn_ValidateIDC(
'
32108519**05026619
'
)
/**/ /*
------
** 大家用合法身份证号测试
*/
/**/ /*
------
** 大家用合法身份证号测试
*/
清理 :
drop function fn_ValidateIDC