mysqlsqldata-scrubbing

Scrub email address from Mysql

I have a MySQL database full of user information. I'd like to give it to a contractor to do some analysis, but I don't want to expose all of my user information. My biggest concern now are the email addresses. I would like to keep the email address domain, but anonymize the address. Ideally, I'd like to to it in a SQL script.

So I'd like to take every item in the 'email' column and turn it from 'myAddress@gmail.com' to 'xxxx@gmail.com' and 'anotherAddress@hotmail.com' to 'xxxx@hotmail.com'. Any ideas?

Solution 
  • UPDATE YourTable
    SET EmailColumn = 'xxx' + RIGHT(EmailColumn, LENGTH(EmailColumn) - LOCATE('@', EmailColumn) + 1)