Buscar y Reemplazar texto con MySQL

532

Si te ves en la necesidad de cambiar de nombre de dominio y mantener el contenido en tu instalación de wordpress, lo mas facil es apuntar el nuevo dominio al servidor actual, y reemplazar el nombre del dominio anterior por el nuevo en la base de datos. Tambien pudiera ser el caso que necesites reemplazar un texto cualquiera y/o corregir problemas de códigos de caracteres cuando hayas migrado de servidor, en cualquier caso si necesitas reemplazar un texto en MySQL aquí están las indicaciones.

Reemplazando texto en MySQL

El comando REPLACE tiene una sintaxis sencilla, descrita de la siguiente forma:

UPDATE tabla SET campo = REPLACE(campo,'texto a buscar','nuevo texto');

Reemplazar nombre del dominio anterior por el nuevo

UPDATE wp_options SET option_value = REPLACE(option_value,'dominioviejo.com','dominionuevo.com');

Asi que utilizando la consulta anterior, podemos personalizarla para nuestro caso y buscar el texto corrupto sobre las tablas que contienen los titulos y articulos en wordpress, post_title y post_content respectivamente:

Reemplazar texto en titulos y contenido de WordPress

Dentro de phpmyadmin podemos ejecutar la siguiente consulta:

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_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, 'í‘', 'Ñ');

Reemplazar texto en tabla comentarios de wordpress

update wp_comments set comment_content = replace(comment_content, '–', '–');
update wp_comments set comment_content = replace(comment_content, 'ñ', 'ñ');
update wp_comments set comment_content = replace(comment_content, 'á', 'á');
update wp_comments set comment_content = replace(comment_content, 'ó', 'ó');
update wp_comments set comment_content = replace(comment_content, 'Ã', 'í');
update wp_comments set comment_content = replace(comment_content, 'íº', 'ú');
update wp_comments set comment_content = replace(comment_content, 'í©', 'é');
update wp_comments set comment_content = replace(comment_content, 'ú', 'ú');
update wp_comments set comment_content = replace(comment_content, 'í‘', 'Ñ');

update wp_comments set comment_author = replace(comment_author, '–', '–');
update wp_comments set comment_author = replace(comment_author, 'ñ', 'ñ');
update wp_comments set comment_author = replace(comment_author, 'á', 'á');
update wp_comments set comment_author = replace(comment_author, 'ó', 'ó');
update wp_comments set comment_author = replace(comment_author, 'Ã', 'í');
update wp_comments set comment_author = replace(comment_author, 'íº', 'ú');
update wp_comments set comment_author = replace(comment_author, 'í©', 'é');
update wp_comments set comment_author = replace(comment_author, 'ú', 'ú');
update wp_comments set comment_author = replace(comment_author, 'í‘', 'Ñ');

Reemplazo para Yet another related post plugin

update wp_yarpp_keyword_cache set body = replace(body, 'ñ', 'ñ');
update wp_yarpp_keyword_cache set body = replace(body, 'á', 'á');
update wp_yarpp_keyword_cache set body = replace(body, 'ó', 'ó');
update wp_yarpp_keyword_cache set body = replace(body, 'Ã', 'í');
update wp_yarpp_keyword_cache set body = replace(body, 'íº', 'ú');
update wp_yarpp_keyword_cache set body = replace(body, 'í‘', 'Ñ');
update wp_yarpp_keyword_cache set body = replace(body, 'ú', 'ú');
update wp_yarpp_keyword_cache set body = replace(body, 'í©', 'é');
update wp_yarpp_keyword_cache set body = replace(body, '–', '–');

update wp_yarpp_keyword_cache set title = replace(title, '–', '–');
update wp_yarpp_keyword_cache set title = replace(title, 'ñ', 'ñ');
update wp_yarpp_keyword_cache set title = replace(title, 'á', 'á');
update wp_yarpp_keyword_cache set title = replace(title, 'ó', 'ó');
update wp_yarpp_keyword_cache set title = replace(title, 'Ã', 'í');
update wp_yarpp_keyword_cache set title = replace(title, 'íº', 'ú');
update wp_yarpp_keyword_cache set title = replace(title, 'í©', 'é');
update wp_yarpp_keyword_cache set title = replace(title, 'ú', 'ú');
update wp_yarpp_keyword_cache set title = replace(title, 'í‘', 'Ñ');

Por el momento asi me ha funcionado, aun tengo pendiente revisar las tablas de comentarios y agregar aca el codigo SQL. Aca podran encontrar un listado de carácteres ISO 8859-1 Latin-1.

Fuente: https://www.guatewireless.org/tecnologia/bases-de-datos/mysql-buscar-y-reemplazar-texto-con-sql/

También podría gustarte
Por favor espere, Cargando...
Do NOT follow this link or you will be banned from the site!