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

SQL 获得某表中身份证信息有效的数据

推荐阅读