1

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?

Tom Hazel
  • 3,232
  • 2
  • 20
  • 20
  • Can you not just `update thetable set email = 'xxx@yyy.com'` on a copy of the db? – Alex K. Jun 17 '11 at 14:04
  • Right, I'm definitely doing this on a copy of the DB, but I wanted 'myAddress@gmail.com' -> 'xxx@gmail.com' and 'anotherAddress@hotmail.com' -> 'xxx@hotmail.com' – Tom Hazel Jun 17 '11 at 14:06

2 Answers2

7
UPDATE YourTable
    SET EmailColumn = 'xxx' + RIGHT(EmailColumn, LENGTH(EmailColumn) - LOCATE('@', EmailColumn) + 1)
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
0
UPDATE YourTable SET EmailColumn = CONCAT( 'xxx', RIGHT(EmailColumn, LENGTH(EmailColumn) - LOCATE('@', user_email) + 1) )
Sergiy Zaharchenko
  • 1,490
  • 1
  • 12
  • 11