John, I think this should work:
UPDATE members SET email=REPLACE(email, SUBSTRING(email,INSTR(email,'@')+1), 'Thanks_in_advance.com.com') Regards, Nathan -----Original Message----- From: John Furlong [mailto:john.furl...@rakutenusa.com] Sent: Monday, June 29, 2009 12:54 PM To: mysql@lists.mysql.com Subject: Update email address domain I'm trying to mask the email addresses for a development database. I need to make all of the domains exactly the same. What is the best way to do this? We have about 67000 distinct domains. I was able to use substring to get the list of domains, but am not sure how to turn that into an update statement SELECT SUBSTRING(email,INSTR(email,'@')+1) AS domain FROM members limit 5; +------------------+ | domain | +------------------+ | aol.com | | verizon.net | | netzero.com | | yahoo.com | | comcast.net | +------------------+ 5 rows in set (0.00 sec) So the full email address will end up as b...@thanks_in_advance.com<mailto:b...@thanks_in_advance.com> j...@thanks_in_advance.com jack@ Thanks_in_advance.com<mailto:%20thanks_in_adva...@netzero.com> a...@thanks_in_advance.com<mailto:a...@thanks_in_advance.com> j...@thanks_in_advance.com John F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
