email cleanup sql

email_cleanup.sql

' replace mydomainnamehere.com
' replace db_name_here

use information_schema;
SET SESSION group_concat_max_len=10000000;
select
    group_concat(
        concat('UPDATE `',`table_name`,'` SET `',`column_name`,'` = REPLACE(`',`column_name`,'`, SUBSTRING(`',`column_name`,'`, LOCATE(''@'', `',`column_name`,'`)), CONCAT(''+'', SUBSTRING(MD5(SUBSTRING(`',`column_name`,'`, LOCATE(''@'', `',`column_name`,'`))) FROM 1 FOR 6), ''@example.com'')) WHERE `',`column_name`,'` NOT LIKE ''%@mydomainnamehere.com'';') SEPARATOR '\n\n') as q
from `columns` as c 
where
table_schema = 'db_name_here' /* UPDATE DATABASE NAME */ 
and (
    (`column_name` like '%email%' and `data_type` in ('varchar', 'text'))
    or (`table_name` like '%core_config_data' and `column_name` = 'value')
)