Instrucción Replace en MySQL

Los que se manejen con WordPress y MySQL es posible que se encuentre en la necesidad de realizar una modificación masiva de caracteres o palabras en una base de datos.

La instrucción para realizarlo es la siguiente, el ejemplo se basa en modificar el contenido de una entrada de WordPress:

[SQL]
update wp_posts set post_content = replace(post_content, ‘Mi cadena’,’Nueva Cadena’);
[/SQL]

Esta instrucción me ha sido especialmente útil en una migración de una base de datos de un servidor Windows a otro Linux donde por un tema de codificación no quedaron bien los acentos, ñ’s y demás caracteres.

Este es el Script de traspaso completo:

[SQL]
update wp_posts set post_content = replace(post_content, ‘á’,’á’);
update wp_posts set post_content = replace(post_content, ‘é’,’é’);
update wp_posts set post_content = replace(post_content, ‘í©’,’é’);
update wp_posts set post_content = replace(post_content, ‘í¨’,’è’);
update wp_posts set post_content = replace(post_content, ‘Ã’,’í’);
update wp_posts set post_content = replace(post_content, ‘í³’,’ó’);
update wp_posts set post_content = replace(post_content, ‘ó’,’ó’);
update wp_posts set post_content = replace(post_content, ‘í²’,’ò’);
update wp_posts set post_content = replace(post_content, ‘íº’,’ú’);

update wp_posts set post_content = replace(post_content, ‘ñ’,’ñ’);
update wp_posts set post_content = replace(post_content, ‘í±’,’ñ’);
update wp_posts set post_content = replace(post_content, ‘º’,’º’);
update wp_posts set post_content = replace(post_content, ‘ª’,’ª’);
update wp_posts set post_content = replace(post_content, ‘€’,’€’);

update wp_posts set post_title = replace(post_title, ‘á’,’á’);
update wp_posts set post_title = replace(post_title, ‘Ã’,’í’);
update wp_posts set post_title = replace(post_title, ‘é’,’é’);
update wp_posts set post_title = replace(post_title, ‘í©’,’é’);
update wp_posts set post_title = replace(post_title, ‘í¨’,’è’);
update wp_posts set post_title = replace(post_title, ‘ó’,’ó’);
update wp_posts set post_title = replace(post_title, ‘í³’,’ó’);
update wp_posts set post_title = replace(post_title, ‘í²’,’ò’);
update wp_posts set post_title = replace(post_title, ‘íº’,’ú’);

update wp_posts set post_title = replace(post_title, ‘º’,’º’);
update wp_posts set post_title = replace(post_title, ‘ª’,’ª’);
update wp_posts set post_title = replace(post_title, ‘ñ’,’ñ’);
update wp_posts set post_title = replace(post_title, ‘í±’,’ñ’);
update wp_posts set post_title = replace(post_title, ‘€’,’€’);

[/SQL]

Autor: Alex Borrás

Informático, especializado en desarrollo Web con WordPress, Redes Sociales y posicionamiento en buscadores (SEO). Fan de la OOP y como afición jugador de Ajedrez. Geek por vocación & iphonero.

Deja un comentario