中華民國身分證字號驗證   Leave a comment

字母對照表 的對照資料下載:

http://cid-9b25575939b45309.skydrive.live.com/embedrow.aspx/%e5%85%ac%e9%96%8b/BLOG%e5%8f%83%e8%80%83%e7%9a%84%e6%aa%94%e6%a1%88/tb|_ValidPersonID|_LetterToNumber.xls

建立表格後,將資料匯入到該表格內

字母對照表 表格結構

———————————————————————————————-

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_ValidPersonID_LetterToNumber](
    [Letter] [varchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
    [Number] [int] NOT NULL,
CONSTRAINT [PK_tb_ValidPersonID_LetterToNumber] PRIMARY KEY CLUSTERED
(
    [Letter] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’字母’ ,@level0type=N’SCHEMA’, @level0name=N’dbo’, @level1type=N’TABLE’, @level1name=N’tb_ValidPersonID_LetterToNumber’, @level2type=N’COLUMN’, @level2name=N’Letter’

GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’數字’ ,@level0type=N’SCHEMA’, @level0name=N’dbo’, @level1type=N’TABLE’, @level1name=N’tb_ValidPersonID_LetterToNumber’, @level2type=N’COLUMN’, @level2name=N’Number’

 

身分證字號驗證函式

———————————————————————————————-

— =============================================
— Author:        林維祥
— Create date: 2008/10/20
— Description:    驗證身分證字號的正確性 會參考 (tb_ValidPersonID_LetterToNumber) 的表格
— TEST:        SELECT dbo.fn_ValidPersonalID(‘A123456786’)
— =============================================

–================流程=====================
/*
5. 判斷所輸入長度是否滿足10個字元
    5.1 不滿足10個字元
    5.2 回傳0
10. 取得第一個字母的所對應的數字
    10.1 無法取得
    10.2 回傳0
15. 針對所對應的個位數數字乘以9,在加上十位數字,其總和再除以10取得餘數
16. 判斷第二個字元是否為字母
    16.1 取得該字母所對應數字的個位數
17. 第二個字元不是字母,將第二個字元直接轉成數字
20. 產生一組10個位數的數列,將數列中的數字依序乘上 1、8、7、6、5、4、3、2’1,後加總起來
25. 將加總後的數字除以10,取餘數
30. 取餘數之補數
31. 如果餘數為0,直接將該補數設為0
35. 判斷餘數的補數是否與檢查碼(身分證字號最後一位數字)相同
    35.1 餘數的補數是否與檢查碼(身分證字號最後一位數字)不相同
    35.2 回傳0
40. 回傳 1
*/
–=========================================
ALTER FUNCTION fn_ValidPersonalID
(
    @PID AS varchar(10)

)
RETURNS bit
AS
BEGIN
    –宣告第一個自願到第十個字元所對應的數字
    DECLARE @L1 AS tinyint
    SET @L1=NULL
    DECLARE @L2 AS tinyint
    SET @L2=NULL
    DECLARE @L3 AS tinyint
    SET @L3=NULL
    DECLARE @L4 AS tinyint
    SET @L4=NULL
    DECLARE @L5 AS tinyint
    SET @L5=NULL
    DECLARE @L6 AS tinyint
    SET @L6=NULL
    DECLARE @L7 AS tinyint
    SET @L7=NULL
    DECLARE @L8 AS tinyint
    SET @L8=NULL
    DECLARE @L9 AS tinyint
    SET @L9=NULL
    DECLARE @L10 AS tinyint
    SET @L10=NULL

    –5. 判斷所輸入長度是否滿足10個字元
    IF(LEN(@PID) < 10)
    BEGIN
    –5.1 不滿足10個字元
        –5.2 回傳0
        return 0
    END
    –10. 取得第一個字母的所對應的數字
    SET @L1 = (SELECT Number
                FROM dbo.tb_ValidPersonID_LetterToNumber
                WHERE Letter=UPPER(SUBSTRING(@PID, 1, 1))
                )
    –10.1 無法取得
    IF(@L1 IS NULL)
    BEGIN
        –10.2 回傳0
        return 0
    END
    –15. 針對所對應的個位數數字乘以9,在加上十位數字,其總和再除以10取得餘數
    SET @L1 =( (@L1*9) + (@L1/10) ) % 10
    –16. 判斷第二個字元是否為字母
    SET @L2 = (SELECT Number
                FROM dbo.tb_ValidPersonID_LetterToNumber
                WHERE Letter=UPPER(SUBSTRING(@PID, 2, 1))
                )
    IF(@L2 IS NOT NULL)
    BEGIN
        –16.1 取得該字母所對應數字的個位數
        SET @L2 = @L2 % 10
    END
    ELSE
    BEGIN
        –17. 第二個字元不是字母,將第二個字元直接轉成數字
        SET @L2=CONVERT(tinyint, SUBSTRING(@PID, 2, 1))
    END

    –20. 產生一組10個位數的數列,將數列中的數字依序乘上 1、8、7、6、5、4、3、2’1,後加總起來   
    SET @L3 = CONVERT(tinyint, SUBSTRING(@PID, 3, 1))
    SET @L4 = CONVERT(tinyint, SUBSTRING(@PID, 4, 1))
    SET @L5 = CONVERT(tinyint, SUBSTRING(@PID, 5, 1))
    SET @L6 = CONVERT(tinyint, SUBSTRING(@PID, 6, 1))
    SET @L7 = CONVERT(tinyint, SUBSTRING(@PID, 7, 1))
    SET @L8 = CONVERT(tinyint, SUBSTRING(@PID, 8, 1))
    SET @L9 = CONVERT(tinyint, SUBSTRING(@PID, 9, 1))
    –檢查碼
    SET @L10 = CONVERT(tinyint, SUBSTRING(@PID, 10, 1))

    DECLARE @Sum AS int
    SET @Sum = (@L1*1) + (@L2*8) + (@L3*7) + (@L4*6) + (@L5*5) + (@L6*4) + (@L7*3) + (@L8*2) + (@L9*1)
    DECLARE @TransCode AS int
    –25. 將加總後的數字除以10,取餘數
    SET @TransCode = @Sum % 10
    –30. 取餘數之補數
    SET    @TransCode = 10 – @TransCode
    –31. 如果餘數為0,直接將該補數設為0
    IF(@TransCode = 10)
        SET @TransCode=0   

    –35. 判斷餘數的補數是否與檢查碼(身分證字號最後一位數字)相同
    IF(@TransCode != @L10)
    BEGIN
    –35.1 餘數的補數是否與檢查碼(身分證字號最後一位數字)不相同
        –35.2 回傳0
        return 0
    END
    ELSE
    BEGIN
        –40. 回傳 1
        return 1
    END
    Return 0
END
GO

廣告

Posted 2008 年 10 月 21 日 by sfcer0414 in MS SQL Store Procedures

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google photo

您的留言將使用 Google 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s

%d 位部落客按了讚: