Change the end of the email domain to a random scan

Question

Hello!

I’m looking for a way to mask emails in the database, but in a way that I can reverse them without much difficulty, in case I need the real email.

My idea is to create a Rigger AFTER INSERT for this. Will not be used in production environment.

My idea was to get emails like:

[email protected]
[email protected]
[email protected]

Turn:

[email protected]
[email protected]
[email protected]

That is, I would like to change the characters after the last point (.) of the email to a random varchar. The size of the replaced text does not need to be the same as the original text.

Does anyone know a quiet way to do this? I don’t know how to take the position of the last point, this seems to be crucial to doing what I want. I wouldn’t want to create a function for this.

I can even mess up the e-mail before the @ with:

 UPDATE pessoa 
 SET email=STUFF(email, 1, CHARINDEX('@', email)-1, LEFT(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 4+ABS(CHECKSUM(NEWID()))%4))

Resulting in:

[email protected]

But it’s not what I want right now.

Answer 1

I did too, otherwise:

update Pessoa
SET email=REVERSE(STUFF(REVERSE(email), 1, CHARINDEX('.', reverse(email)) - 1, RIGHT(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 3)))

Answer 2

You can do it like this:

UPDATE pessoa 
 SET email = CONCAT(substring(email, 1, (LEN(email) - CHARINDEX('.',REVERSE(email))) + 1), LEFT(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 4+ABS(CHECKSUM(NEWID()))%4))

First is taking the position of the last point, as it does not have a native function for this (at least I do not know and found nothing) is used the REVERSE to reverse the string and the CHARINDEX stops at the first occurrence. After that it takes the value of the field up to that position + 1 and adds the random characters.