Nathan,
That was exactly what I was looking for, thanks for your help.
John
-----Original Message-----
From: Nathan Sullivan [mailto:nsullivan@stripped]
Sent: Monday, June 29, 2009 2:55 PM
To: John Furlong; mysql@stripped
Subject: RE: Update email address domain
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.Furlong@stripped]
Sent: Monday, June 29, 2009 12:54 PM
To: mysql@stripped
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
bill@stripped<mailto:bill@stripped>
joe@stripped
jack@ Thanks_in_advance.com<mailto:%20Thanks_in_advance@stripped>
amy@stripped<mailto:amy@stripped>
jill@stripped
John F
