Partially masking an email address-CodePudding

Hi all I am having a little difficulty masking email addresses using SQL Developer (Oracle).

What I want to do is substitute all characters before the @ symbol in an email address except for the first and the last digit. For instance if my email address is:

[email protected]

then I would like the output to display m*****[email protected]

So far I have been using the replace, substr and instr functions and can get the right part to work but I do not know how to also enable it to only show the first character given an email character count varies. This is my attempt so far,

SELECT name, REPLACE(emailAddress,substr(emailAddress, instr(emailAddress, '@')-3,2),'***') emailMask

FROM  myEmailTable

I thought perhaps Oracle's REGEXP_REPLACE and REGEXP_INSTR might be more suitable. Or I thought maybe I could extract the first letter from the email column and then concatenate it.

Any advice would be really appreciated. I have inserted a table below to help illustrate what I am trying to achieve.

name	emailAddress	emailMask
Bon Jovi	[email protected]	B*****[email protected]
Dan Brown	[email protected]	D****[email protected]

Thank you !

CodePudding user response：

For me, simple option works the best.

Sample data:

SQL> with test (emailaddress) as
  2    (select '[email protected]'     from dual union all
  3     select '[email protected]'      from dual union all
  4     select '[email protected]' from dual
  5    ),

Query begins here: extract part of the email address that precedes the @ sign, find its length (so that you'd know parameters for the LPAD function, and the last part (that follows the @ sign).

  6  temp as
  7    (select emailaddress,
  8            substr(emailaddress, 1, instr(emailaddress, '@') - 1) first_part,
  9            length(substr(emailaddress, 1, instr(emailaddress, '@') - 1)) len,
 10            substr(emailaddress, instr(emailaddress, '@')) second_part
 11     from test
 12    )

Finally, concatenate the first letter to asterisks and the last letter, along with the whole second part of the email address:

 13  select emailaddress,
 14         substr(first_part, 1, 1) || lpad('*', len - 2, '*') || substr(first_part, -1)
 15           || second_part as result
 16  from temp;

EMAILADDRESS          RESULT
--------------------- ------------------------------
[email protected]     B*****[email protected]
[email protected]      D****[email protected]
[email protected] L*********[email protected]

SQL>

CodePudding user response：

This is what I've come up with: Take the first character, pad it to the desired length with *, then add the rest.

select
  rpad(substr(emailaddress, 1, 1), instr(emailaddress, '@') - 2, '*') ||
  substr(emailaddress, instr(emailaddress, '@') - 1)
from mytable;

CodePudding user response：

You are almost there. Just a little change in the REGEXP_REPLACE parameters from my side like so:

select column_value, regexp_replace(column_value, '(.).*?(.?@.*)', '\1***\2') rr 
from table(sys.odciVarchar2List('', 'a', 'abc', 'a@bc', 'ab@', 'ab@c', 'abcd@efgh'))

The fiddle of the above http://sqlfiddle.com/#!4/68b32/6145

CodePudding user response：

You can use REGEXP_REPLACE to replace the characters before @. Your following SQL should look like this.

select name,
       emailAddress,
       regexp_replace(emailAddress, '(.). ', '\1') || lpad('*', length(regexp_replace(emailAddress, '.(. ).@. ', '\1')), '*') || regexp_replace(emailAddress, '. (.)@(. )', '\1@\2') emailMask
from myEmailTable

. means any character.
. means any character (min. one or more).

Reference:

REGEXP_REPLACE