SQL 获得某表中身份证信息有效的数据
参考文章:中华人民共和国居民身份证【http://zh.wikipedia.org/wiki/%E4%B8%AD%E5%8D%8E%E4%BA%BA%E6%B0%91%E5%85%B1%E5%92%8C%E5%9B%BD%E5%B1%85%E6%B0%91%E8%BA%AB%E4%BB%BD%E8%AF%81#.E6.A0.A1.E9.AA.8C.E7.A0.81.E8.AE.A1.E7.AE.97.E6.96.B9.E6.B3.95】
--18位的: SELECT * FROM Customer WHERE IDCard IS NOT NULL AND LEN(IDCard)=18 --长度限制 AND Isnumeric(Substring(IDCard,1,17)) =1 AND PATINDEX('%.%',Substring(IDCard,1,17)) = 0 --前17位是整数 AND ISDATE(Substring(IDCard,7,4)+'-'+Substring(IDCard,11,2)+'-'+Substring(IDCard,13,2)) =1 --第8~14位可以构成日期类型 AND Substring(IDCard,7,4)+'-'+Substring(IDCard,11,2)+'-'+Substring(IDCard,13,2) BETWEEN '1900-01-01' AND '2079-06-06' --日期时间在‘1900-01-01’~‘2079-06-06’之间 AND substring(IDCard,18,1) = dbo.GetCheckIDCardCode(IDCard) --最后一位是否正确 --15位的: SELECT *FROM Customer WHERE IDCard IS NOT NULL AND LEN(IDCard)=15 --长度限制 AND ISNUMERIC(IDCard) =1 AND PATINDEX('%.%',IDCard) = 0 --是整数 AND ISDATE( '19'+ SUBSTRING(IDCard,7,2)+'-'+SUBSTRING(IDCard,9,2)+'-'+SUBSTRING(IDCard,11,2)) =1 --第8~12位可以构成日期类型 AND '19'+ SBUSTRING(IDCard,7,2)+'-'+SUBSTRING(IDCard,9,2)+'-'+SUBSTRING(IDCard,11,2) BETWEEN '1900-01-01' AND '2079-06-06' --日期时间在‘1900-01-01’~‘2079-06-06’之间 /* ISNUMERIC : 当输入运算式为有效数值类型(int,bigint,smallint,tinyint,decimal)时,ISNUMERIC 就会传回 1,否则便传回 0。 PATINDEX: 返回在指定表达式中第一次出现的起始位置;如果在所有有效的文本和字符数据类型中都找不到该模式,则返回 0。 ISDATE: 如果 expression 是有效的 date、time 或 datetime 值,则返回 1;否则,返回 0。(如果 expression 为 datetime2 值,则 ISDATE 返回 0) */
其中跟据国家规定的计算公式,计算18位身份证最后一位的dbo.GetCheckIDCardCode如下:
CREATE FUNCTION GetCheckIDCardCode(@sfzh CHAR(18)) RETURNS CHAR(1) AS BEGIN DECLARE @r VARCHAR(2) DECLARE @i INT IF LEN(@sfzh) <> 18 SET @r = 0 ELSE SET @i = CAST(SUBSTRING(@sfzh,1,1) AS INT) * 7 +CAST(SUBSTRING(@sfzh,2,1) AS INT) * 9 +CAST(SUBSTRING(@sfzh,3,1) AS INT) * 10 +CAST(SUBSTRING(@sfzh,4,1) AS INT) * 5 +CAST(SUBSTRING(@sfzh,5,1) AS INT) * 8 +CAST(SUBSTRING(@sfzh,6,1) AS INT) * 4 +CAST(SUBSTRING(@sfzh,7,1) AS INT) * 2 +CAST(SUBSTRING(@sfzh,8,1) AS INT) * 1 +CAST(SUBSTRING(@sfzh,9,1) AS INT) * 6 +CAST(SUBSTRING(@sfzh,10,1) AS INT) * 3 +CAST(SUBSTRING(@sfzh,11,1) AS INT) * 7 +CAST(SUBSTRING(@sfzh,12,1) AS INT) * 9 +CAST(SUBSTRING(@sfzh,13,1) AS INT) * 10 +CAST(SUBSTRING(@sfzh,14,1) AS INT) * 5 +CAST(SUBSTRING(@sfzh,15,1) AS INT) * 8 +CAST(SUBSTRING(@sfzh,16,1) AS INT) * 4 +CAST(SUBSTRING(@sfzh,17,1) AS INT) * 2 SET @i = @i - @i/11 * 11 SET @r = CAST((CASE @i WHEN 0 THEN 1 WHEN 1 THEN 0 WHEN 2 THEN 11 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 ELSE '' END) AS CHAR) IF (@r = 11) SET @r='X' ELSE SET @r = @r SET @r = '' + @r +'' RETURN @r END