Change the last part of the e-mail domain to a random varchar

Question

Change the last part of the e-mail domain to a random varchar

Navigation

#1 by (3 votes)
#2 by (2 votes)

2

Hello

I'm looking for a way to mask emails in the database, but in a way that I can revert to them without much difficulty if I need the actual email.

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

My idea was to make emails like:

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

Turn around:

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

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

Does anyone know a quiet way to do this? I do not know how to take the position of the last point, this seems to be crucial to do what I want. I would not like to create a function for this.

I can even mess up the email before @ 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.

sql sql-server

asked by anonymous 15.08.2018 / 20:51

2 answers

3

I also managed, in another way:

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

15.08.2018 / 21:15

Converting word file to pdf Laravel - Use OR within where

score 2 · Accepted Answer

You can do 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 getting the position of the last point, since it does not have a native function for this (at least I do not know and I did not find anything), the REVERSE is used to invert the string and the CHARINDEX to in the first occurrence. After that it takes the value of the field up to that position + 1 and adds the random characters.