I have multiple email ids in a table. but i need mask all the email ids using oracle SQL. Below are some examples of masking email ids,
Input : Output :
1. alex.hales@gmail.com = ****.hales@*****.com
2. Joeroot.eng@yahoo.co.in = *****ot.eng@*****.**.in
I want to:
First Part:
For each email ids first 5 charters from the left hand side of '@' should be masked with *, but if a '.' is coming within the first character then it should not be masked it will remain same.
Second Part:
right hand side from '@' will be masked with *, again if a '.' is there then it will remain same and the domain name like .com, .in should be as it is.
Thanks in advance.
What I have tried:
Declare
v_id Varchar2(100):= 'abc.defghijklmnop@qrst.uv.wxyz';
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:::
abc.defghijklmnop@qrst.uv.wxyz.
4
18
***.**fghijklmnop@
SELECT REGEXP_REPLACE (substr(instr('abc.defghijklmnop@qrstname.uv.wxyz','@')+1),'^[.].'*')
FROM dual;
Output:
abc.defghijklmnop@****.**.****
I am not able to unmasked the domain name as well as unable to do the whole thing together. Could any one please help me out.