Updating email addresses in MySQL (regexp?)

Question

Hi,

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

Didn't check it, since don't have mysql installed, but seems it could help you

update table_name 
set table_name.email = substr(table_name.email, 0, position("@" in table_name.email) + 1) 
                       + "new_domain";

PS. Regexp won't help you for update, since it only can help you to locate specific entrance of substring in string ot check whenever string is matches the pattern. Here you can find reference to relevant functions.

Answer 2

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.

Answer 3

I would rather do it with PHP, if possible. Mysql unfortunately does not allow capturing matching parts in regular expressions. Or even better: you can combine the two like this, for example:

$emails = fetchAllDistinctEmailsIntoAnArray();
# Make the array int-indexed:
$emails = array_values($emails);
# convert the mails
$replacedEmails = preg_replace('/xx$/', 'yy', $emails);
# create a new query
$cases = array();
foreach ($emails as $key => $email) {
    # Don't forget to use mysql_escape_string or similar!
    $cases[] = "WHEN '" . escapeValue($email) .
               "' THEN '" . escappeValue(replacedEmails[$key]) . "'";
}
issueQuery(
    "UPDATE `YourTable`
    SET `emailColumn` =
        CASE `emailColumn`" .
            implode(',', $cases) .
        " END CASE");

Note that this solution will take quite some time and you may run out of memory or hit execution limits if you have many entries in your database. You might want to look into ignore_user_abort() and ini_set() for changing the memory limit for this script.

Disclaimer: Script not tested! Do not use without understanding/testing the code (might mess up your db).