性校验MSSQL验证身份证号码的合法性

1. 身份证号码的格式与校验

身份证号码是中国公民唯一的身份标识，在很多情况下都需要进行验证其合法性。身份证号码由18位数字与字母组成，其中最后一位为校验码。

1.1 身份证号码的格式

身份证号码由以下几部分组成：

6位地区编号

8位出生日期

3位顺序号

1位校验码

其中，地区编号与出生日期是固定的，顺序号是随机生成的。校验码是通过前17位数字计算得出的。

1.2 身份证号码的校验

校验码是通过前17位数字进行计算得出的。计算方法如下：

将身份证号码前17位数字依次乘以对应的权重，然后将得到的结果相加。

将第一步得到的结果与11取模，得到一个余数。

根据余数得到校验码。

其中，权重的计算方法为：

-- 权重数组

DECLARE @weights int[] = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};
-- 计算身份证号码的校验码
CREATE FUNCTION calcCheckCode(@idcard varchar(18))
RETURNS varchar(1)
AS
BEGIN
    DECLARE @sum int = 0;
    -- 计算前17位数字的加权和
    SELECT @sum = SUM(CONVERT(int, SUBSTRING(@idcard, num, 1)) * @weights[num])
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17)) digits(num)
    -- 计算余数
    DECLARE @mod int = @sum % 11;
    -- 根据余数得到校验码
    DECLARE @checkcode varchar(1) = 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;
    RETURN @checkcode;
END;

以上代码是一个计算身份证号码校验码的函数。

2. 在MSSQL中进行身份证号码的性校验

使用上面的校验函数可以很容易地在MSSQL中进行身份证号码的性校验。

-- 创建一个验证身份证号码的函数

CREATE FUNCTION validateIdcard(@idcard varchar(18))
RETURNS bit
AS
BEGIN
    -- 校验身份证号码是否为18位数字或字母
    IF LEN(@idcard) != 18 OR PATINDEX('%[^0-9a-zA-Z]%', @idcard) > 0
    BEGIN
        RETURN 0;
    END;
    -- 计算校验码
    DECLARE @checkcode varchar(1) = dbo.calcCheckCode(@idcard);
    -- 验证校验码是否正确
    IF @checkcode != SUBSTRING(@idcard, 18, 1)
    BEGIN
        RETURN 0;
    END;
    RETURN 1;
END;

以上代码创建了一个名为validateIdcard的函数，可以接受一个身份证号码作为参数，返回一个bit型值。当身份证号码合法时返回1，否则返回0。

可以通过以下方式调用该函数：

SELECT dbo.validateIdcard('110101199003072354');

注意： 在实际应用中，也可以通过在数据库中设置约束（如CHECK约束）来实现身份证号码的合法性校验。