sql身份證號最後四位加密

⑴ SQL函數驗證身份證號碼是否有效

SQL函數驗證身份證號碼是否有效
比如身份證號在C列，D2：
=IF(MID("10X98765432",MOD(SUM(MID(C2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*2^(18-{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17})),11)+1,1)=RIGHT(C2),"身份證正確","身份證錯誤")
數組公式，按CTRL+SHIFT+回車結束，下拉。

⑵ sql 語句 驗證身份證號碼

像這樣寫個視圖就行了：

create View eVMutiCard
AS
Select a.Badge,a.Name,a.DepID,a.Compid,a.JobID,a.Status,a.EmpType,a.ReportTo,
b.Identification,
N'身份證長度不合常理' As Remark
From employee b
Where (Len(b.Identification) Not In (15,18)
And b.Identification Is Not Null )
Or b.Identification is Null
Union All
Select a.Badge,a.Name,a.DepID,a.Compid,a.JobID,a.Status,a.EmpType,a.ReportTo,
b.Identification,
N'身份證具有無效字元' As Remark
From employee b
Where Len(b.Identification) In (15,18)
And Isnumeric(Case Len(b.Identification) When 18 Then Substring(b.Identification,1,17)
Else b.Identification End) = 0
Union All
Select a.Badge,a.Name,a.DepID,a.Compid,a.JobID,a.Status,a.EmpType,a.ReportTo,
b.Identification,
N'身份證出生日期不合常理' As Remark
From employee b
Where Len(b.Identification) In (15,18)
And (IsDate(Case When Len(b.Identification)=15 Then '19'+Substring(b.Identification,7,2)+'-'+Substring(b.Identification,9,2)+'-'+Substring(b.Identification,11,2)
Else Substring(b.Identification,7,4)+'-'+Substring(b.Identification,11,2)+'-'+Substring(b.Identification,13,2)
End)=0
Or Not (
(Case When Len(b.Identification)=15 Then '19'+Substring(b.Identification,7,2)+'-'+Substring(b.Identification,9,2)+'-'+Substring(b.Identification,11,2)
Else Substring(b.Identification,7,4)+'-'+Substring(b.Identification,11,2)+'-'+Substring(b.Identification,13,2)
End) Between '1900-01-01' And '2079-06-06'))
Union All
Select a.Badge,a.Name,a.DepID,a.Compid,a.JobID,a.Status,a.EmpType,a.ReportTo,
b.Identification,
N'身份證校驗位不正確(第18位與校驗不符)' As Remark
From employee b
Where (Len(b.Identification) = 18
And substring(b.Identification,18,19) <> dbo.GetCheckIDCardCode(b.Identification)
And b.Identification Is Not Null)

其中跟據國家規定的計算公式，計算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數據表其中包含很多身份證號，將該身份證進行檢索，根據地區、年齡段等分類統計

不用正則表達式，用substr就可以了
地區是前六位，你可以上網查一下含義，統計可以取substr(id,1,6)
生日是第七位開始的八位數substr(id,7,8)