MySql replace con RegExp

MySql purtroppo non possiede una funzione di tipo replace abbinata ad una regular expression, come altri db.

Mi è capitato di dover effettuare una bonifica su una tabella che aveva un campo di tipo text con una serie di indirizzi mail separati dal carattere ;.

La modifica consisteva nel rimpiazzare tutti gli indirizzi di un certo dominio (poniamo ad esempio @xyz ) con una mail precisa, lasciando ovviamente inalterati gli altri indirizzi

Pensavo quindi di utilizzare una funzione del tipo replace(VALORE_CAMPO,’.*@xyz’,’mioindirizzo@xyz’), utilizzando quindi una regular expression per fare il match.

Purtroppo invece la replace di Mysql non suppporta regular expression.

Ho quindi dovuto realizzare il seguente work around con 3 stored procedures:

1. La stored sprocedure split che effettua lo split dei valori in base al ;, inserendo i record risultanti su tabella (MySql non dispone di una funzione split);
2. La stored procedure sp_lavoraMessaggio che dato in input l’id Primary Key della tabella dei messaggi da modificare chiama la procedura split sul campo degli indirizzi mail, quindi sulla tabella creata da split effettua le replace del caso e poi recupera tutti i dati dalla tabella di appoggio per effettuare l’update nel campo text della tabella da bonificare;
3. La stored procedure cleanMail che legge dalla tabella da bonificare tutte le occorrenze da modificare e quindi per ogni occorrenza richiama la stored procedure del punto 2

Di seguito posto il codice delle 3 stored procedures:

Split

DELIMITER $$ DROP PROCEDURE IF EXISTS `split` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `split`(input text, delim VARCHAR(10)) begin /** SCRIVE I DATI SPLITTATI SU UNA TABELLA DI APPOGGIO, CHE OGNI VOLTA DROPPA E RICREA **/ declare foundPos tinyint unsigned; declare tmpTxt text; declare delimLen tinyint unsigned; declare element text; drop  table if exists tmpValues; create  table tmpValues ( `valori` text not null ) engine = MyIsam; set delimLen = length(delim); set tmpTxt = input; set foundPos = instr(tmpTxt,delim); while foundPos <> 0 do set element = substring(tmpTxt, 1, foundPos-1); set tmpTxt = replace(tmpTxt, concat(element,delim), ''); insert into tmpValues (`valori`) values ( element); set foundPos = instr(tmpTxt,delim); end while; if tmpTxt <> '' then insert into tmpValues (`valori`) values (tmpTxt); end if; END $$ DELIMITER ;

sp_lavoraMessaggio

DELIMITER $$ DROP PROCEDURE IF EXISTS `db`.`sp_lavoraMessaggio` $$ CREATE PROCEDURE `db`.`sp_lavoraMessaggio` (in idMessaggio int) BEGIN declare listaDestinatariAppo text; declare listaDestinatariTable text; declare listaDestinatari text; DECLARE doneCursorValori INT DEFAULT 0; declare cursorValori cursor for select concat(valori,';') from tmpvalues; DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneCursorValori = 1; set listaDestinatari=""; select destinatari into listaDestinatariTable from messaggio_email where id_messaggio=idMessaggio; call split(listaDestinatariTable,';'); update tmpValues set valori=’mioindirizzo@xyz.it' where valori like '%@xyz%'; open cursorValori; /** QUI SCORRE I VALORI DALLA TABELLA SPLITTATA E LI ACCODA ALLA NUOVA LISTA DESTINATARI **/ repeat fetch cursorValori into listaDestinatariAppo; if not doneCursorValori then set listaDestinatari=concat(listaDestinatari,listaDestinatariAppo); end if; until doneCursorValori end repeat; close cursorValori; /** A QUESTO PUNTO EFFETTUA UPDATE, TOGLIENDO L’ULTIMO ; ALLA LISTA PRECEDENTEMENTE OTTENUTA **/ update messaggio_email set destinatari=substring(listaDestinatari,1,char_length(listaDestinatari)-1) where id_messaggio=idMessaggio; END $$ DELIMITER ;

cleanMail

DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_cleanMail` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_cleaniMail`() BEGIN declare idMessaggio int; declare cont int; DECLARE doneIdMessaggi INT DEFAULT 0; /**        RECUPERO MESSAGGI DA BONIFICARE **/ declare  cursoreMessaggi  cursor for select id_messaggio from messaggio_email where data_invio is null; DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneIdMessaggi = 1; set cont=0; open cursoreMessaggi; repeat fetch cursoreMessaggi into idMessaggio; if not doneIdMessaggi then call sp_lavoraMessaggio(idMessaggio); set cont=cont+1; end if; UNTIL doneIdMessaggi END REPEAT; close cursoreMessaggi; select concat('Procedura terminata con successo, messaggi lavorati: ',cont); END $$ DELIMITER ;