Question

SQL替换身份证号码，数据量大概1000多万，替换规则如下图

SQL替换身份证号码，数据量大概1000多万，替换规则如下图，注意身份有15位和18位的，还有尾号带X的。急哦，采纳可再加分。

Answer 1

一次性处理有点难度。可以写循环脚本。逐条读取再处理。估计会很慢。除非你服务器很好。
或者写个函数，需要时候就自处理。但是原数据还是不变。

追问

恩，就是担心服务器负荷过大，所以看能不能直接用sql语句在 sql sever里直接操作数据库，不经过另外的语句转换。

Answer 2

速度是个问题

追问

用sql 语句，不用写程序转换

追答

用sql语句也一样，功能不是问题，速度是大问题

追问

恩，大虾，类似正则表达式之类的麻烦您能不能帮写写看，速度先不管，挂在那让它执行一两天都问题不大。谢谢

追答

;with temp as (
select distinct LEFT(S,6)as C6
from test with (nolock)
)
,temp2 as
(select '1' as C,'0x0' as CB
union all
select '2' as C,'A2F' as CB
union all
select '3' as C,'8FB' as CB
union all
select '4' as C,'34F' as CB
union all
select '5' as C,'88A' as CB
union all
select '6' as C,'932' as CB
union all
select '7' as C,'86E' as CB
union all
select '8' as C,'E2F' as CB
union all
select '9' as C,'EEB' as CB
union all
select '0' as C,'23F' as CB
)
,temp3 as(
select C6,Tc1.CB+Tc2.CB+Tc3.CB+Tc4.CB+Tc5.CB+Tc6.CB as C6B
from temp
inner join temp2 as Tc1 on substring(temp.C6,1,1)=Tc1.C
inner join temp2 as Tc2 on substring(temp.C6,2,1)=Tc2.C
inner join temp2 as Tc3 on substring(temp.C6,3,1)=Tc3.C
inner join temp2 as Tc4 on substring(temp.C6,4,1)=Tc4.C
inner join temp2 as Tc5 on substring(temp.C6,5,1)=Tc5.C
inner join temp2 as Tc6 on substring(temp.C6,6,1)=Tc6.C
)

update test set S2=temp3.C6B+SUBSTRING(S,7,20) from test
inner join temp3 on temp3.C6=LEFT(test.S,6)
写了一个测试，本机笔记本执行了一下1000w条大概几分钟

追问

好的。谢谢，我先测试，回头给你采纳。把答案也共享出去，帮助更多的人。