【笔记】Oracle表中身份证、手机、邮箱脱敏SQL

        前天开始居家办公，用向日葵远程办公室电脑，但是昨天办公室的网崩了，导致无法远程，于是开始用腾讯云做数据库，为防止数据泄露就有了下边的关键数据脱敏操作。

笔记如下

select * from job_xs where xh='U201311735';




--REPLACE(<字段名>,SUBSTR(<字段名>,2,1), “*”) AS <字段名>
update job_xs set sfzh = REPLACE(sfzh,SUBSTR(sfzh,7,8), '********') 
where  length(sfzh)=18;

--手机号脱敏;
UPDATE XYDB_RZRJ
    SET lxdh = ( CASE WHEN lxdh IS NOT NULL THEN 
                    SUBSTR (lxdh , 1 , 3) || '*****' || SUBSTR (lxdh , 9) 
               ELSE NULL END ) 
               where uuid='16ce4c60-de9a-4015-9e56-5af86fd49482';
               
--工号脱敏;
UPDATE XYDB_RZRJ
    SET XMLXRGH = ( CASE WHEN XMLXRGH IS NOT NULL THEN 
                    SUBSTR (XMLXRGH , 1 , 3) || '*****' || SUBSTR (XMLXRGH , 9) 
               ELSE NULL END );
               
--身份证脱敏;
UPDATE XYDB_RZRJ 
    SET sfzjh = ( CASE WHEN sfzjh IS NOT NULL THEN 
                    SUBSTR (sfzjh , 1 , 3) || '***********' || SUBSTR (sfzjh , 15) 
               ELSE NULL END );
               
--邮箱脱敏;
UPDATE XYDB_RZRJ
    SET dzxx = ( CASE WHEN dzxx IS NOT NULL THEN 
                    SUBSTR (dzxx , 1 , 3) || '*****' || SUBSTR (dzxx , 9)
               ELSE NULL END ) 
               where uuid='16ce4c60-de9a-4015-9e56-5af86fd49482';