Updating email addresses in MySQL (regexp?)

Question

Is there a way to update every email address in MySQL with regexp? What I want to do is to change [email protected] addresses to [email protected]. Is it possible to do with SQL or should I do it with PHP for example?

Thanks!

Answer 1

You can search for a REGEXP with MySQL, but, unfortunately, it cannot return the matched part.

It's possible to do it with SQL as follows:

UPDATE  mytable
SET     email = REPLACE(email, '@domain.xx', '@domain.yy')
WHERE   email REGEXP '@domain.xx$'

You can omit the WHERE clause, but it could lead to unexpected results (like @example.xxx.com will be replaced with @example.yyx.com), so it's better to leave it.