MySQL anonymize e-mail

26 07 2011

Working with MySQL I encountered a task I’ve never faced before. I had to anonymize (mask) e-mail addresses in a table. In first attempt I googled this article on StackOverflow. That give me a feeling that some functions useful for this issue should be already implemented in MySQL. I was right. I found string functions documentation and finally I ended up with the following solution:

select concat(substring_index(email, '@', 1), '@yopmail.com') from societe;

It changes e-mails from:

jean.dupont@yahoo.fr

to:

jean.dupont@yopmail.com

Alternatively, you can change username and leave the string after ‘@’.

select concat('user@', substring_index(email, '@', -1)) from societe;

And the result is:

user@yahoo.fr