34,590
社区成员
发帖
与我相关
我的任务
分享
-- =============================================
-- 作 者: 黄江华
-- 日 期: 2008-05-27
-- 描 述: 身份证号码是否有效
-- 备 注:
-- 身份证号只有15或18位
-- 如果是15位身份证 则只验证日期和是否数字格式
-- 18位身份证 验证日期 校验位
-- =============================================
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fun_IsValidID')
AND type in ('IF', 'FN', 'TF'))
DROP FUNCTION dbo.fun_IsValidID
GO
CREATE FUNCTION dbo.fun_IsValidID(@ID Varchar(18))
-- Add the parameters for the stored procedure here
RETURNS BIT
AS
BEGIN
DECLARE @ValidFactors VARCHAR(17),
@ValidCodes VARCHAR(11),
@I TINYINT,
@iTemp INT
--位数不满足则为非法ID
IF Len(@ID) <> 15 AND Len(@ID) <> 18
RETURN(0);
--如果是15位身份证 则只验证日期和是否数字格式
IF LEN(@ID)=15
IF ISDATE('19'+SUBSTRING(@ID,7,6))=0 OR ISNUMERIC(@ID)=0
RETURN(0);
ELSE
RETURN(1);
/*18位身份证 验证日期 校验位 */
--验证日期和前17位是否数字格式
IF ISDATE(SUBSTRING(@ID,7,8))=0 OR ISNUMERIC(SUBSTRING(@ID,1,17))=0
RETURN(0);
--验证校验位开始
SELECT @ValidFactors='79A584216379A5842',
@ValidCodes='10X98765432',
@I=1,@iTemp=0
WHILE @i<18
BEGIN
SELECT
@iTemp=@iTemp+CAST(SUBSTRING(@ID,@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(@ID,1)
RETURN(1);
ELSE
RETURN(0);
RETURN NULL;
END
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fun_IsValidID')
AND type in ('IF', 'FN', 'TF'))
DROP FUNCTION dbo.fun_IsValidID
GO
CREATE FUNCTION dbo.fun_IsValidID(@ID Varchar(18))
-- Add the parameters for the stored procedure here
RETURNS BIT
AS
BEGIN
DECLARE @ValidFactors VARCHAR(17),
@ValidCodes VARCHAR(11),
@I TINYINT,
@iTemp INT
--位数不满足则为非法ID
IF Len(@ID) <> 15 AND Len(@ID) <> 18
RETURN(0);
--如果是15位身份证 则只验证日期和是否数字格式
IF LEN(@ID)=15
IF ISDATE('19'+SUBSTRING(@ID,7,6))=0 OR ISNUMERIC(@ID)=0
OR LEFT(@ID,15) NOT LIKE REPLICATE('[0-9]',15)
RETURN(0);
ELSE
RETURN(1);
/*18位身份证 验证日期 校验位 */
--验证日期和前17位是否数字格式
IF ISDATE(SUBSTRING(@ID,7,8))=0 OR LEFT(@ID,17) NOT LIKE REPLICATE('[0-9]',17)
RETURN(0);
--验证校验位开始
SELECT @ValidFactors='79A584216379A5842',
@ValidCodes='10X98765432',
@I=1,@iTemp=0
WHILE @i<18
BEGIN
SELECT
@iTemp=@iTemp+CAST(SUBSTRING(@ID,@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(@ID,1)
RETURN(1);
ELSE
RETURN(0);
RETURN NULL;
END
GO
select dbo.fun_IsValidID('12345619120304d567')
select dbo.fun_IsValidID('12345619120304d')
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fun_IsValidID')
AND type in ('IF', 'FN', 'TF'))
DROP FUNCTION dbo.fun_IsValidID
GO
Create FUNCTION dbo.fun_IsValidID(@ID Varchar(18))
-- Add the parameters for the stored procedure here
RETURNS BIT
AS
BEGIN
DECLARE @ValidFactors VARCHAR(17),
@ValidCodes VARCHAR(11),
@I TINYINT,
@iTemp INT,
@Str Varchar(1)
--位数不满足则为非法ID
IF Len(@ID) <> 15 AND Len(@ID) <> 18
RETURN(0);
--如果是15位身份证 则只验证日期和是否数字格式
IF LEN(@ID)=15
IF ISDATE('19'+SUBSTRING(@ID,7,6))=0 OR ISNUMERIC(@ID)=0
RETURN(0);
ELSE
RETURN(1);
/*18位身份证 验证日期 校验位 */
--验证日期和前17位是否数字格式
IF ISDATE(SUBSTRING(@ID,7,8))=0 OR ISNUMERIC(SUBSTRING(@ID,1,17))=0
RETURN(0);
--验证校验位开始
SELECT @ValidFactors='79A584216379A5842',
@ValidCodes='10X98765432',
@I=1,@iTemp=0
WHILE @i<18
BEGIN
Set @Str = SUBSTRING(@ID,@i,1)
if (ASCII(@Str) < 48) Or (ASCII(@Str) > 57) RETURN(0)
SELECT
@iTemp=@iTemp+CAST(@Str 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(@ID,1)
RETURN(1);
ELSE
RETURN(0);
RETURN NULL;
END
-- =============================================
-- 作 者: 黄江华
-- 日 期: 2008-05-27
-- 描 述: 身份证号码是否有效
-- 备 注:
-- 身份证号只有15或18位
-- 如果是15位身份证 则只验证日期和是否数字格式
-- 18位身份证 验证日期 校验位
-- =============================================
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fun_IsValidID')
AND type in ('IF', 'FN', 'TF'))
DROP FUNCTION dbo.fun_IsValidID
GO
CREATE FUNCTION dbo.fun_IsValidID(@ID Varchar(18))
-- Add the parameters for the stored procedure here
RETURNS BIT
AS
BEGIN
DECLARE @ValidFactors VARCHAR(17),
@ValidCodes VARCHAR(11),
@I TINYINT,
@iTemp INT
--位数不满足则为非法ID
IF Len(@ID) <> 15 AND Len(@ID) <> 18
RETURN(0);
--如果是15位身份证 则只验证日期和是否数字格式
IF LEN(@ID)=15
IF ISDATE('19'+SUBSTRING(@ID,7,6))=0 OR ISNUMERIC(@ID)=0
RETURN(0);
ELSE
RETURN(1);
/*18位身份证 验证日期 校验位 */
--验证日期和前17位是否数字格式
IF ISDATE(SUBSTRING(@ID,7,8))=0 OR ISNUMERIC(SUBSTRING(@ID,1,17))=0
RETURN(0);
declare @maco varchar(200);set @maco=left(@id,17)
select @maco=
substring(@maco,patindex('%[0-9.]%',@maco),
patindex('%[^0-9.]%',substring(@maco,patindex('%[0-9.]%',@maco),
len(@maco)-patindex('%[0-9.]%',@maco)+1))-1)
if(len(@maco)<>17)
return (0)
--验证校验位开始
SELECT @ValidFactors='79A584216379A5842',
@ValidCodes='10X98765432',
@I=1,@iTemp=0
WHILE @i<18
BEGIN
SELECT
@iTemp=@iTemp+CAST(SUBSTRING(@ID,@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(@ID,1)
RETURN(1);
ELSE
RETURN(0);
RETURN NULL;
END
CREATE FUNCTION dbo.fun_IsValidID(@ID Varchar(18))
-- Add the parameters for the stored procedure here
RETURNS BIT
AS
BEGIN
DECLARE @ValidFactors VARCHAR(17),
@ValidCodes VARCHAR(11),
@I TINYINT,
@iTemp INT
--位数不满足则为非法ID
IF Len(@ID) <> 15 AND Len(@ID) <> 18
RETURN(0);
--如果是15位身份证 则只验证日期和是否数字格式
IF LEN(@ID)=15
IF ISDATE('19'+SUBSTRING(@ID,7,6))=0 OR @ID NOT LIKE REPLICATE('[0-9]',15)
RETURN(0);
ELSE
RETURN(1);
/*18位身份证 验证日期 校验位 */
--验证日期和前17位是否数字格式
IF ISDATE(SUBSTRING(@ID,7,8))=0 OR LEFT(@ID,17) NOT LIKE REPLICATE('[0-9]',17)
RETURN(0);
--验证校验位开始
SELECT @ValidFactors='79A584216379A5842',
@ValidCodes='10X98765432',
@I=1,@iTemp=0
WHILE @i<18
BEGIN
SELECT
@iTemp=@iTemp+CAST(SUBSTRING(@ID,@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(@ID,1)
RETURN(1);
ELSE
RETURN(0);
RETURN NULL;
END
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fun_IsValidID')
AND type in ('IF', 'FN', 'TF'))
DROP FUNCTION dbo.fun_IsValidID
GO
CREATE FUNCTION dbo.fun_IsValidID(@ID Varchar(18))
RETURNS BIT
AS
BEGIN
declare @sTemp varchar(8)
declare @ValidCode table(id int,Factors int)
declare @Valid int
--创建行政区划表并插入一些数据
declare @xzqh table(id char(6))
insert into @xzqh select 110101 union all select 110102 union all select 110103 union all select 110104 union all select 110105 union all
select 120104 union all select 120105 union all select 120106 union all select 120110 union all select 120111 union all select 120112 union all
select 120225 union all select 130101 union all select 130102 union all select 130103 union all select 130104 union all select 130105
--位数验证
IF Len(@ID) <> 15 AND Len(@ID) <> 18
RETURN(0);
--验证区划
if not exists(select 1 from @xzqh where id=left(@id,6))
return(0);
--日期验证
set @sTemp=(case when len(@id)=15 then '19'+substring(@id,7,6) else substring(@id,7,8) end)
IF ISDATE(@sTemp)=0
RETURN(0);
--性别码验证
set @sTemp=(case when len(@id)=15 then right(@id,3) else substring(@id,15,3) end)
if isnumeric(@sTemp)=0
return(0)
if len(@id)=15
return(1)
--验证校验位
insert into @ValidCode select 1,7 union all select 2,9 union all select 3,10 union all select 4,5 union all select 5,8 union all
select 6,4 union all select 7,2 union all select 8,1 union all select 9,6 union all select 10,3 union all select 11,7 union all
select 12,9 union all select 13,10 union all select 14,5 union all select 15,8 union all select 16,4 union all select 17,2
;with cte as(
select 1 id,left(@id,1) c,right(@id,len(@id)-1) c1
union all
select id+1,left(c1,1),right(c1,len(c1)-1) from cte where len(c1)>0
)select @valid=(12-sum(a.c*b.factors)%11)%11 from cte a inner join @ValidCode b on a.id=b.id
if right(@id,1)=(case when @valid=10 then 'X' else ltrim(@valid) end)
return(1)
else
return(0)
RETURN NULL;
END
go
select dbo.fun_IsValidID('110104198003110516')
select dbo.fun_IsValidID('13010219831213044X')
/*
1
*/
create table xzqh(id char(6),qhname nvarchar(16))
insert into xzqh select '110000','北京市' union all
select '110100','市辖区' union all
select '110101','东城区' union all
select '110102','西城区' union all
select '110103','崇文区' union all
select '110104','宣武区' union all
......
select '659004','五家渠市' union all
select '710000','台湾省' union all
select '810000','香港特别行政区' union all
select '820000','澳门特别行政区'
go
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fun_IsValidID')
AND type in ('IF', 'FN', 'TF'))
DROP FUNCTION dbo.fun_IsValidID
GO
CREATE FUNCTION dbo.fun_IsValidID(@ID Varchar(18))
RETURNS BIT
AS
BEGIN
declare @sTemp varchar(8)
declare @ValidCode table(id int,Factors int)
declare @Valid int
--位数验证
IF Len(@ID) <> 15 AND Len(@ID) <> 18
RETURN(0);
--验证区划
if not exists(select 1 from xzqh where id=left(@id,6))
return(0);
--日期验证
set @sTemp=(case when len(@id)=15 then '19'+substring(@id,7,6) else substring(@id,7,8) end)
IF ISDATE(@sTemp)=0
RETURN(0);
--性别码验证
set @sTemp=(case when len(@id)=15 then right(@id,3) else substring(@id,15,3) end)
if isnumeric(@sTemp)=0
return(0)
if len(@id)=15
return(1)
--验证校验位
insert into @ValidCode select 1,7 union all select 2,9 union all select 3,10 union all select 4,5 union all select 5,8 union all
select 6,4 union all select 7,2 union all select 8,1 union all select 9,6 union all select 10,3 union all select 11,7 union all
select 12,9 union all select 13,10 union all select 14,5 union all select 15,8 union all select 16,4 union all select 17,2
;with cte as(
select 1 id,left(@id,1) c,right(@id,len(@id)-1) c1
union all
select id+1,left(c1,1),right(c1,len(c1)-1) from cte where len(c1)>0
)select @valid=(12-sum(a.c*b.factors)%11)%11 from cte a inner join @ValidCode b on a.id=b.id
if right(@id,1)=(case when @valid=10 then 'X' else ltrim(@valid) end)
return(1)
else
return(0)
RETURN NULL;
END
go
select dbo.fun_IsValidID('37162419880519037X')
select dbo.fun_IsValidID('510623198003110517')
/*
1
*/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fun_IsValidID')
AND type in ('IF', 'FN', 'TF'))
DROP FUNCTION dbo.fun_IsValidID
GO
CREATE FUNCTION dbo.fun_IsValidID(@ID Varchar(18))
-- Add the parameters for the stored procedure here
RETURNS BIT
AS
BEGIN
DECLARE @ValidFactors VARCHAR(17),
@ValidCodes VARCHAR(11),
@I TINYINT,
@iTemp INT
--位数不满足则为非法ID
IF Len(@ID) <> 15 AND Len(@ID) <> 18
RETURN(0);
--如果是15位身份证 则只验证日期和是否数字格式
IF LEN(@ID)=15
IF ISDATE('19'+SUBSTRING(@ID,7,6))=0 OR ISNUMERIC(@ID)=0
RETURN(0);
ELSE
RETURN(1);
/*18位身份证 验证日期 校验位 */
--验证日期和前17位是否数字格式
IF ISDATE(SUBSTRING(@ID,7,8))=0 OR ISNUMERIC(SUBSTRING(@ID,1,17))=0
RETURN(0);
--验证校验位开始
SELECT @ValidFactors='79A584216379A5842',
@ValidCodes='10X98765432',
@I=1,@iTemp=0
IF LEFT(@ID,17) NOT LIKE REPLICATE('[0-9]',17) RETURN 0
ELSE
BEGIN
WHILE @i<18
BEGIN
SELECT
@iTemp=@iTemp+CAST(SUBSTRING(@ID,@i,1) AS INT)*(CASE SUBSTRING(@validFactors,@i,1) WHEN 'A' THEN 10 ELSE SUBSTRING(@ValidFactors,@i,1) END)
,@i=@i+1
END
END
IF SUBSTRING(@ValidCodes,@iTemp%11+1,1)=RIGHT(@ID,1)
RETURN(1);
ELSE
RETURN(0);
RETURN NULL;
END
GO
-- =============================================
-- 作 者: 黄江华
-- 日 期: 2008-05-27
-- 描 述: 身份证号码是否有效
-- 备 注:
-- 身份证号只有15或18位
-- 如果是15位身份证 则只验证日期和是否数字格式
-- 18位身份证 验证日期 校验位
-- =============================================
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fun_IsValidID')
AND type in ('IF', 'FN', 'TF'))
DROP FUNCTION dbo.fun_IsValidID
GO
CREATE FUNCTION dbo.fun_IsValidID(@ID Varchar(18))
-- Add the parameters for the stored procedure here
RETURNS BIT
AS
BEGIN
declare @maco varchar(200);set @maco=left(@id,17)
select @maco=
substring(@maco,patindex('%[0-9.]%',@maco),
patindex('%[^0-9.]%',substring(@maco,patindex('%[0-9.]%',@maco),
len(@maco)-patindex('%[0-9.]%',@maco)+1))-1)
if(len(@maco)<>17)
return (0)
DECLARE @ValidFactors VARCHAR(17),
@ValidCodes VARCHAR(11),
@I TINYINT,
@iTemp INT
--位数不满足则为非法ID
IF Len(@ID) <> 15 AND Len(@ID) <> 18
RETURN(0);
--如果是15位身份证 则只验证日期和是否数字格式
IF LEN(@ID)=15
IF ISDATE('19'+SUBSTRING(@ID,7,6))=0 OR ISNUMERIC(@ID)=0
RETURN(0);
ELSE
RETURN(1);
/*18位身份证 验证日期 校验位 */
--验证日期和前17位是否数字格式
IF ISDATE(SUBSTRING(@ID,7,8))=0 OR ISNUMERIC(SUBSTRING(@ID,1,17))=0
RETURN(0);
--验证校验位开始
SELECT @ValidFactors='79A584216379A5842',
@ValidCodes='10X98765432',
@I=1,@iTemp=0
WHILE @i<18
BEGIN
SELECT
@iTemp=@iTemp+CAST(SUBSTRING(@ID,@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(@ID,1)
RETURN(1);
ELSE
RETURN(0);
RETURN NULL;
END
-- =============================================
-- 作 者: 黄江华
-- 日 期: 2008-05-27
-- 描 述: 身份证号码是否有效
-- 备 注:
-- 身份证号只有15或18位
-- 如果是15位身份证 则只验证日期和是否数字格式
-- 18位身份证 验证日期 校验位
-- =============================================
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.fun_IsValidID')
AND type in ('IF', 'FN', 'TF'))
DROP FUNCTION dbo.fun_IsValidID
GO
CREATE FUNCTION dbo.fun_IsValidID(@ID Varchar(18))
-- Add the parameters for the stored procedure here
RETURNS BIT
AS
BEGIN
DECLARE @ValidFactors VARCHAR(17),
@ValidCodes VARCHAR(11),
@I TINYINT,
@iTemp INT
--位数不满足则为非法ID
IF Len(@ID) <> 15 or Len(@ID) <> 18
RETURN(0);
--如果是15位身份证 则只验证日期和是否数字格式
IF LEN(@ID)=15
IF ISDATE('19'+SUBSTRING(@ID,7,6))=0 OR ISNUMERIC(@ID)=0
RETURN(0);
/*18位身份证 验证日期 校验位 */
--验证日期和前17位是否数字格式
IF ISDATE(SUBSTRING(@ID,7,8))=0 OR ISNUMERIC(SUBSTRING(@ID,1,17))=0
RETURN(0);
--验证校验位开始
SELECT @ValidFactors='79A584216379A5842',
@ValidCodes='10X98765432',
@I=1,@iTemp=0
WHILE @i<18
BEGIN
SELECT
@iTemp=@iTemp+CAST(SUBSTRING(@ID,@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(@ID,1)
RETURN(1);
ELSE
RETURN(0);
RETURN NULL;
END