如何在不屏蔽其域名和 oracle SQL 中的点的情况下屏蔽电子邮件 ID

Question

我在一个表中有多个电子邮件ID。但是我需要使用 oracle SQL 屏蔽所有电子邮件 ID。以下是屏蔽电子邮件 ID 的一些示例，

输入 ： 输出 ：

1. [e-mail] = ****.hales@*****.com
2. [e-mail] = *****ot.eng@*****.**.in

我想：

第一部分：

对于每个电子邮件 ID，@左侧的前 5 个包机应该用 * 屏蔽，但如果.出现在第一个字符内，那么它不应该被屏蔽，它将保持不变。

第二部分：

@的右侧将被*掩盖，同样，如果存在.，那么它将保持不变，并且域名.com，.in应该是原样。

我试过：

enter code here
Declare
v_id Varchar2(100):= '[e-mail]';
v_id1 Varchar2(100);
v_id2 Varchar2(100);
v_id3 Varchar2(100);
v_id4 Varchar2(100);
v_id5 Varchar2(100);
v_id6 Varchar2(100);
Begin
Select v_id,
instr(v_id,'.'),
instr(v_id,'@'),
Case When instr(v_id,'.') >=6
   Then '*****'|| substr(v_id,6,(instr(v_id,'@')-5))
Else
rpad(lpad('.',instr(v_id,'.'),'*'),6,'*') || substr(v_id,7,(instr(v_id,'@')- 6))
End testing
Into v_id1,v_id2,v_id3,v_id4
From dual;
dbms_output.put_line(v_id1);
dbms_output.put_line(v_id2);
dbms_output.put_line(v_id3);
dbms_output.put_line(v_id4);
End;
o/p:::
[e-mail]. 
4
18
***.**fghijklmnop@

SELECT REGEXP_REPLACE (substr(instr('[e-mail]','@')+1),'^[.].'*')
FROM dual;
Output:
abc.defghijklmnop@****.**.****

我无法解开域名的屏蔽，也无法一起做整个事情。

Answer 1

您应该搜索类似 Oracle 数据掩码和子集的内容。

<小时 />

仅使用文本操作：

WITH test_data AS 
(
        SELECT '[e-mail]' AS email FROM dual
        UNION ALL
        SELECT '[e-mail]' FROM dual
        UNION ALL
        SELECT '[e-mail]' FROM dual
        UNION ALL
        SELECT 't1@z.a' FROM dual
)
SELECT z.email,
    CASE WHEN INSTR(part1_masked, '*',1, 5) > 0 
          THEN SUBSTR(part1_masked,1,INSTR(part1_masked, '*',1, 5)) 
               || SUBSTR(part1, INSTR(part1_masked, '*',1, 5)+1)
            ELSE part1_masked
    END || '@' 
    || SUBSTR(part2_masked,1,INSTR(part2_masked, '.',-1)-1)
    || SUBSTR(part2,INSTR(part2, '.',-1,1)) AS masked_email 
FROM
(
    SELECT 
      email
     ,SUBSTR(t.email, 1, INSTR(t.email, '@')-1) AS part1
     ,SUBSTR(t.email,INSTR(t.email, '@')+1) AS part2
     ,regexp_replace(SUBSTR(t.email, 1, INSTR(t.email, '@')-1),
                            '[[:alnum:]]', '*') AS part1_masked
     ,regexp_replace(SUBSTR(t.email,INSTR(t.email, '@')+1),
                            '[[:alnum:]]', '*') AS part2_masked
    FROM test_data t    
) z

LiveDemo

输出：

╔═══════════════════════════════╦═══════════════════════════════╗
║             EMAIL             ║         MASKED_EMAIL          ║
╠═══════════════════════════════╬═══════════════════════════════╣
║ [e-mail]          ║ ****.*ales@*****.com          ║
║ [e-mail]       ║ *****ot.eng@*****.**.in       ║
║ [e-mail] ║ *.*.*.*.*x.ampl.e@**.**.*.com ║
║ t1@z.a                        ║ **@*.*                        ║
╚═══════════════════════════════╩═══════════════════════════════╝

工作原理：

1. SUBSTR(t.email, 1, INSTR(t.email, '@')-1) AS part1 - 拆分电子邮件(@是分隔符(

2. regexp_replace(SUBSTR(t.email, 1, INSTR(t.email, '@')-1), '[[:alnum:]]', '*') AS part1_masked - 将所有 alfanumeric 字符替换为 *

3. INSTR(part1_masked, '*',1, 5) > 0 - 检查屏蔽字符串中是否存在至少 5 *

连接

4. 来自屏蔽字符串的最多 5 颗星的部分，连接未屏蔽字符串的其余部分

编辑：

WITH test_data AS 
(
        SELECT '[e-mail]' AS email FROM dual
        UNION ALL
        SELECT '[e-mail]' FROM dual
        UNION ALL
        SELECT '[e-mail]' FROM dual
        UNION ALL
        SELECT 't1@z.a' FROM dual
)
SELECT z.email,
     CASE WHEN INSTR(part1, '.')-1 < 0 THEN part1_masked
          WHEN INSTR(part1, '.')-1 <=5 
          THEN SUBSTR(part1_masked,1, INSTR(part1, '.')) 
               || SUBSTR(part1, INSTR(part1, '.')+1)
           ELSE SUBSTR(part1_masked, 1, 5) || SUBSTR(part1, 6)
      END
    || '@' 
    || SUBSTR(part2_masked,1,INSTR(part2_masked, '.',-1)-1)
    || SUBSTR(part2,INSTR(part2, '.',-1,1)) AS masked_email 
FROM
(
    SELECT 
      email
     ,SUBSTR(t.email, 1, INSTR(t.email, '@')-1) AS part1
     ,SUBSTR(t.email,INSTR(t.email, '@')+1) AS part2
     ,regexp_replace(SUBSTR(t.email, 1, INSTR(t.email, '@')-1),
                            '[[:alnum:]]', '*') AS part1_masked
     ,regexp_replace(SUBSTR(t.email,INSTR(t.email, '@')+1),
                            '[[:alnum:]]', '*') AS part2_masked
    FROM test_data t    
) z

LiveDemo2

Ouptut：

╔═══════════════════════════════╦═══════════════════════════════╗
║             EMAIL             ║         MASKED_EMAIL          ║
╠═══════════════════════════════╬═══════════════════════════════╣
║ [e-mail]          ║ ****.hales@*****.com          ║
║ [e-mail]       ║ *****ot.eng@*****.**.in       ║
║ [e-mail] ║ *.e.s.t.ex.ampl.e@**.**.*.com ║
║ t1@z.a                        ║ **@*.a                        ║
╚═══════════════════════════════╩═══════════════════════════════╝