![[info]](http://l-stat.livejournal.com/img/userinfo.gif?v=92.1){kind=small}
![[info]](http://l-stat.livejournal.com/img/community.gif?v=92.1){kind=small}
Say I have a table that looks like this..
EMAIL
-----
jon@mydomain.com
ted@mydomain.com
jill@mydomain.com
nikki@mydomain.com
bob@mydomain.com
.
.
.
And I want to make an SQL statement to change the domain of the email address so the records look like this:
jon@anotherdomain.com
ted@anotherdomain.com
jill@anotherdomain.com
nikki@anotherdomain.com
bob@anotherdomain.com
Has anyone ever done anything like this? Sort of a search-and-replace via SQL. Any help would be appreciated.
Randy
September 25 2003, 08:04:59 UTC 8 years ago
use REPLACE()
http://www.mysql.com/doc/en/String_funcUPDATE table_name SET Email = REPLACE(Email, 'old_domain','new_domain')
Test it by doing SELECT Email, REPLACE(Email, 'old_domain','new_domain') FROM table_name before hand to make sure it's what you want.
September 25 2003, 10:19:10 UTC 8 years ago
Re: use REPLACE()
That'll work great... if you can assert that old_domain never appears before the '@' as part of a username.If you can't, that won't work... perfectly. But even then, there are ways to fix the update statement so it will.
If you can assert this, then no worries.
September 25 2003, 11:46:26 UTC 8 years ago
Re: use REPLACE()
UPDATE table_name SET Email = REPLACE(Email, '@old_domain','@new_domain')September 25 2003, 11:54:20 UTC 8 years ago
Re: use REPLACE()
Bingo; I was thinking of using some combination of LOCATE(), INSTR(), and SUBSTRING(), but this is much better... but you still have to be able to assert there's only one '@' in every email address... which you can easily do with a simple query.September 25 2003, 12:11:30 UTC 8 years ago
Re: use REPLACE()
Yeah.Besides all that, I think a better way to do this would be to have two sperate fields anyway.
September 25 2003, 23:50:55 UTC 8 years ago
Re: use REPLACE()
If I'm not mistaken, RFC says there can be only one @ in an email address.September 26 2003, 00:03:19 UTC 8 years ago
Re: use REPLACE()
You are mistaken.September 26 2003, 07:34:57 UTC 8 years ago
Re: use REPLACE()
Ha.Oh, man.
:-)
September 26 2003, 08:26:43 UTC 8 years ago
you sure about that?
Can you point me to the resource? I can't seem to find the spec. online.In any case, it shouldn't be that hard to verify that his data doesn't include any of these, with a
SELECT COUNT(*) FROM tablename WHERE email LIKE '%@%@%';
no?
September 26 2003, 10:35:56 UTC 8 years ago
Re: you sure about that?
All RFCs can be found at http://www.faqs.orgRFC are just reccommendations, but basically if you want your stuff to play nicely with everyone else's stuff and you want it to work as expected, you need to follow the RFCs.
September 26 2003, 12:14:36 UTC 8 years ago
Re: you sure about that?
Yeah, you could do that.I don't know what the performance characteristics of that are... so you may want to use LOCATE and SUBSTRING to find the first '@', then chop off anything before it, and check if there's an '@' after that (which would also flag the foo@bar@baz@buh condition).
As for the spec, keep reading... it's in the RFCs, but I'll point it out in a comment below (gotta find it).
Granted, this discussion is kinda stupid... you should be sanitizing database content upon input. But there are a lot of "stupid" developers out there, so sometimes you can't assert facts about a system which would make life easier if you could assert them.
This is a case of being thorough about that, and will result in better software.
September 26 2003, 10:33:07 UTC 8 years ago
Re: use REPLACE()
From my reading of RFC 2822 section 3.4.1, which obs RFC 822, valid email address format is dot-atom"@"dot-atom, dot-atom being atom and ".", atom being a string of atext characters, and atext being any alpha-numeric or any of the special characters list. "@" is not in the list of atext characters. I'd be intrested to see what RFC you were reading.September 26 2003, 12:24:06 UTC 8 years ago
Re: use REPLACE()
RFC 2821, section 3.1.3 suggests acceptance of all valid RFC 822 addresses.Multiple '@' signs in an email address were used for routing and relaying purposes.
So it's perfectly valid (albeit, not used anymore) to have an address with multiple \0x40s; RFC 2822 (or 2821... can't remember) also suggests "obsolete" addressing formats be accepted and processed according to established best practice standards.
So the relevant RFC does not guarantee that all email address you ever encounter have to have one '@' in them; backwards compatibility is a bitch, ain't it? ;-)
September 25 2003, 10:22:32 UTC 8 years ago
...that's how I would go about it...but, once again I reiterate, I'm far from a SQL master...
September 25 2003, 12:01:11 UTC 8 years ago
back to the "subject"
Incidentally, if this doesn't suffice, and you want to use actual regexes, MySQL has a decent implementation of them.(Good to bear in mind when doing queries or building tools to search your DBs.)
That said, I did not realize you could do a "replace" like this in a single MySQL statement; I would have resorted to a script.
Thanks for the info!