Update email address domain

--_000_0E7CADE245D3EA448593847B5228A70E2167A250A8NYMSlsdlink sy_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I'm trying to mask the email addresses for a development database. I need t=
o 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@Thanks_in_advance.com
joe@Thanks_in_advance.com
jack@ Thanks_in_advance.com
amy@Thanks_in_advance.com
jill@Thanks_in_advance.com




John F

--_000_0E7CADE245D3EA448593847B5228A70E2167A250A8NYMSlsdlink sy_--

RE: Update email address domain

John,

I think this should work:

UPDATE members SET email=3DREPLACE(email, SUBSTRING(email,INSTR(email,'@')+=
1), 'Thanks_in_advance.com.com')


Regards,
Nathan

-----Original Message-----
From: John Furlong [mailto:John.Furlong@rakutenusa.com]=20
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 t=
o 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@Thanks_in_advance.com
joe@Thanks_in_advance.com
jack@ Thanks_in_advance.com
amy@Thanks_in_advance.com
jill@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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Update email address domain

Nathan,

That was exactly what I was looking for, thanks for your help.

John

-----Original Message-----
From: Nathan Sullivan [mailto:nsullivan@cappex.com]=20
Sent: Monday, June 29, 2009 2:55 PM
To: John Furlong; mysql@lists.mysql.com
Subject: RE: Update email address domain

John,

I think this should work:

UPDATE members SET email=3DREPLACE(email, SUBSTRING(email,INSTR(email,'@')+=
1), 'Thanks_in_advance.com.com')


Regards,
Nathan

-----Original Message-----
From: John Furlong [mailto:John.Furlong@rakutenusa.com]=20
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 t=
o 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@Thanks_in_advance.com
joe@Thanks_in_advance.com
jack@ Thanks_in_advance.com
amy@Thanks_in_advance.com
jill@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=3Dgcdmg-mysql-2@m.gmane.o rg