¿Los artículos de tu blog publicados aquí? ¡Propón tu blog!

Instrucción Replace en MySQL

Publicado el 14 noviembre 2011 por Alexborras @alexborras

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:

PLAIN TEXT SQL: UPDATE wp_posts SET post_content = REPLACE(post_content, 'Mi cadena','Nueva Cadena');

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

Este es el Script de traspaso completo:

PLAIN TEXT 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, '€','€');