本文主要介紹了SQL Server 2005的身份證函數,其中包含驗證和15位轉18位。
具體示例代碼如下：
以下為引用的內容：
USE [LzmTWWorks]
GO
/****** 對象: UserDefinedFunction [Helper].[IDCard] 腳本日期: 12/13/2007 19:16:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Helper].[IDCard]
(
@Card varchar(18)
)
RETURNS
@TCard TABLE
(
Input varchar(18)
,IDCard varchar(18)
,Valid bit
)
AS
BEGIN
DECLARE
@Input as varchar(18)
,@IDCard as varchar(18)
,@Valid as bit
DECLARE
@Length as smallint
,@TmpCard as varchar(18)
,@IsOld as bit
SET @Valid = 0
SET @IDCard = ''
SET @Input = ''
IF @Card IS NULL GOTO Finish
SET @Input = LTRIM(RTRIM(@Card)) /*去空格*/
SET @Length = LEN(@Input)
IF NOT @Length IN (15, 18) GOTO Finish /*非15、18位*/
IF @Length = 15
BEGIN
IF ISNUMERIC(@Input) = 0 GOTO Finish /*非數字*/
SET @TmpCard = LEFT(@Input, 6) + '19' + RIGHT(@input, 9) /*補充為17位*/
SET @IsOld = 1
END
ELSE
BEGIN
IF ISNUMERIC(LEFT(@Input, 17)) = 0 GOTO Finish /*非數字*/
SET @TmpCard = LEFT(@Input, 17) /*取前17位*/
SET @IsOld = 0
END
DECLARE @Birthday varchar(8)
SET @Birthday = SUBSTRING(@TmpCard, 7, 8)
IF ISDATE(@birthday) = 0 GOTO Finish /*非日期*/
--前17位數與相應加權因子積的和
DECLARE
@Sum as smallint
,@WI as tinyint
,@Index as tinyint
,@Num as tinyint
SET @Sum = 0
SET @Index = 1
WHILE @Index < 18
BEGIN
SET @Num = CAST(SUBSTRING(@TmpCard, @Index, 1) AS tinyint)
SELECT @WI =
CASE @Index
WHEN 1 THEN 7
WHEN 2 THEN 9
WHEN 3 THEN 10
WHEN 4 THEN 5
WHEN 5 THEN 8
WHEN 6 THEN 4
WHEN 7 THEN 2
WHEN 8 THEN 1
WHEN 9 THEN 6
WHEN 10 THEN 3
WHEN 11 THEN 7
WHEN 12 THEN 9
WHEN 13 THEN 10
WHEN 14 THEN 5
WHEN 15 THEN 8
WHEN 16 THEN 4
WHEN 17 THEN 2
END
SET @Sum = @Sum + @Num * @WI
SET @Index = @Index + 1
END
--模11
DECLARE @Mod as tinyint
SET @Mod = @Sum % 11
--校驗碼
DECLARE @Parity as varchar(1)
SELECT @Parity =
CASE @Mod
WHEN 0 THEN '1'
WHEN 1 THEN '0'
WHEN 2 THEN 'X'
WHEN 3 THEN '9'
WHEN 4 THEN '8'
WHEN 5 THEN '7'
WHEN 6 THEN '6'
WHEN 7 THEN '5'
WHEN 8 THEN '4'
WHEN 9 THEN '3'
WHEN 10 THEN '2'
END
--完整的18位身份證號碼
SET @TmpCard = @TmpCard + @Parity
IF @IsOld = 1
SET @Valid = 1
ELSE
IF @Parity = RIGHT(@Input, 1) /*校驗*/
SET @Valid = 1
--無論對錯，都給出有效身份證號碼
SET @IDCard = @tmpCard
Finish:
INSERT INTO @TCard VALUES(@Input, @IDCard, @Valid)
RETURN
END
只需使用下面的語句,即可列出所有不符的身份證號碼。
以下為引用的內容：
SELECT
[姓名]
,[身份證號]
,b.*
FROM [EmployeeWorks].[Base].[職員]
CROSS APPLY [LzmTWWorks].[Helper].[IDCard](身份證號) b
WHERE NOT [身份證號] IS NULL AND Valid = 0
補充信息：
以下為引用的內容：
USE [LzmTWWorks]
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [Helper].[IDCard]
(
@Card varchar(18)
)
RETURNS
@TCard TABLE
(
Input varchar(18)
,IDCard varchar(18)
,Sex bit
,Birthday varchar(8)
,Region varchar(6)
,RegionName nvarchar(50)
,RegionFullName nvarchar(100)
,Valid bit
)
AS
BEGIN
DECLARE
@Input varchar(18)
,@IDCard varchar(18)
,@Sex bit
,@Birthday varchar(8)
,@Region varchar(6)
,@RegionName varchar(50)
,@RegionFullName varchar(100)
,@Valid bit
DECLARE
@Length as smallint
,@TmpCard as varchar(18)
,@IsOld as bit
SET @Valid = 0
SET @IDCard = ''
SET @Input = ''
IF @Card IS NULL GOTO Finish
SET @Input = LTRIM(RTRIM(@Card)) /*去空格*/
SET @Length = LEN(@Input)
IF NOT @Length IN (15, 18) GOTO Finish /*非15、18位*/
IF @Length = 15
BEGIN
IF ISNUMERIC(@Input) = 0 GOTO Finish /*非數字*/
SET @TmpCard = LEFT(@Input, 6) + '19' + RIGHT(@input, 9) /*補充為17位*/
SET @IsOld = 1
END
ELSE
BEGIN
IF ISNUMERIC(LEFT(@Input, 17)) = 0 GOTO Finish /*非數字*/
SET @TmpCard = LEFT(@Input, 17) /*取前17位*/
SET @IsOld = 0
END
SET @Birthday = SUBSTRING(@TmpCard, 7, 8)
IF ISDATE(@birthday) = 0 GOTO Finish /*非日期*/
--前17位數與相應加權因子的積的和
DECLARE
@Sum as smallint
,@WI as tinyint
,@Index as tinyint
,@Num as tinyint
SET @Sum = 0
SET @Index = 1
WHILE @Index < 18
BEGIN
SET @Num = CAST(SUBSTRING(@TmpCard, @Index, 1) AS tinyint)
SELECT @WI =
CASE @Index
WHEN 1 THEN 7
WHEN 2 THEN 9
WHEN 3 THEN 10
WHEN 4 THEN 5
WHEN 5 THEN 8
WHEN 6 THEN 4
SET @Sum = @Sum + @Num * @WI
SET @Index = @Index + 1
END
--模11
DECLARE @Mod as tinyint
SET @Mod = @Sum % 11
--校驗碼
DECLARE @Parity as varchar(1)
SELECT @Parity =
CASE @Mod
WHEN 0 THEN '1'
WHEN 1 THEN '0'
WHEN 2 THEN 'X'
WHEN 3 THEN '9'
WHEN 4 THEN '8'
WHEN 5 THEN '7'
WHEN 6 THEN '6'
WHEN 7 THEN '5'
WHEN 8 THEN '4'
WHEN 9 THEN '3'
WHEN 10 THEN '2'
END
--完整的18位身份證號碼
SET @TmpCard = @TmpCard + @Parity
IF @IsOld = 1
SET @Valid = 1
ELSE
IF @Parity = RIGHT(@Input, 1) /*校驗*/
SET @Valid = 1
--無論正確與否，都給出有效身份證號碼
SET @IDCard = @tmpCard
--取其它信息
SET @Sex = SUBSTRING(@tmpCard, 17, 1) % 2
SET @Region = SUBSTRING(@tmpCard, 1, 6)
SELECT
@RegionName = [Name]
,@RegionFullName = [Full]
FROM [Helper].[RegionCodeFullName](Default, @Region)
/*從最新版本數據中取區域信息*/
IF @RegionName IS NULL
BEGIN
DECLARE @FirstDate varchar(8)
/*第一代身份證的區劃碼，目前很多已經不在使用。
所以，需要從最舊版本的數據中取區域信息*/
SELECT @FirstDate = MIN(FirstDate)
FROM [Private].[RegionCode]
SELECT
@RegionName = [Name]
,@RegionFullName = [Full]
FROM [Helper].[RegionCodeFullName](
@FirstDate
,@Region)
END
Finish:
INSERT INTO @TCard
VALUES(
@Input
,@IDCard
,@Sex
,@Birthday
,@Region
,@RegionName
,@RegionFullName
,@Valid)
RETURN
END
示例:
以下為引用的內容：
SELECT * FROM [LzmTWWorks].[Helper].[IDCard] ('110116200808080010')
/*
Input IDCard Sex Birthday Region RegionName RegionFullName Valid
---------- ----------- ---- ----- -------- ------ ------------ ------------ -----
110116200808080010 110116200808080014 1 20080808 110116 寬城區 長春市寬城區 0
*/
