Updating email addresses in MySQL (regexp?)

Question

Is there a way to update every email address in MySQL using regexp? What I want to do is change the address [e-mail] to [e-mail]. Is it possible to do with SQL or should I do it with PHP, for example?

Thanks!

Answer 1

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

This can be done using SQL as follows:

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

You can omit the WHERE , but this may lead to unexpected results (for example, @example.xxx.com will be replaced by @example.yyx.com ), so it is better to leave it.

Answer 2

 UPDATE tableName SET email = CONCAT(SUBSTRING(email, 1, locate('@',email)), 'domain.yy') WHERE email REGEXP '@domain.xx$'; 

Answer 3

I would prefer to do this with PHP, if possible. Unfortunately, Mysql does not allow you to capture the corresponding parts in regular expressions. Or even better: you can combine these two, 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"); 

Please note that this solution will take a lot of time, and you may run out of memory or press execution limits if you have many records in your database. You might want to look into ignore_user_abort() and ini_set() to change the memory limit for this script.

Disclaimer: Script not verified! Do not use without understanding / testing the code (it can ruin your db).

Answer 4

Not tested since I don't have mysql, but it seems like this might help you

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

PS. Regexp will not help you with the update, as it can help you find the specific substring input in the ot line if the whole line matches the pattern. Here you can find a link to the corresponding functions.