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]

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Cargando…

Deja un comentario