qtranslate x cleanup queries

# QTRANSLATE CLEANUP QUERIES # create temp column to separate post content on <!--more--> tag to simplify queries ALTER TABLE `wp_posts` ADD `tmp_excerpt` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL; # split content ## fill `tmp_exceprt` column UPDATE wp_posts SET tmp_excerpt = SUBSTRING( post_content FROM 1 FOR LOCATE( '<!--more-->', post_content ) - 1 ) ; ## fill strip the excerpt from `post_content` column UPDATE wp_posts SET post_content = case when LOCATE('<!--more-->', post_content) > 0 then SUBSTRING( post_content FROM LOCATE( '<!--more-->', post_content ) + 11 ) else post_content end; # clean the qTranslate data, leaving only english ## `post_content` ### strip after "en" content UPDATE wp_posts SET post_content = case when LOCATE('<!--:en-->', post_content) > 0 then SUBSTRING( post_content FROM 1 FOR LOCATE( '<!--:-->', post_content, LOCATE('<!--:en-->', post_content) ) - 1 ) else post_content end; ### strip before "en" content UPDATE wp_posts SET post_content = case when LOCATE('<!--:en-->', post_content) > 0 then SUBSTRING( post_content FROM LOCATE( '<!--:en-->', post_content ) + 10 ) else post_content end; ## `tmp_excerpt` ### strip after "en" content UPDATE wp_posts SET tmp_excerpt = case when LOCATE('<!--:en-->', tmp_excerpt) > 0 then SUBSTRING( tmp_excerpt FROM 1 FOR LOCATE( '<!--:-->', tmp_excerpt, LOCATE('<!--:en-->', tmp_excerpt) ) - 1 ) else tmp_excerpt end; ### strip before "en" content UPDATE wp_posts SET tmp_excerpt = case when LOCATE('<!--:en-->', tmp_excerpt) > 0 then SUBSTRING( tmp_excerpt FROM LOCATE( '<!--:en-->', tmp_excerpt ) + 10 ) else tmp_excerpt end; ## `post_title` ### strip after "en" content UPDATE wp_posts SET post_title = case when LOCATE('<!--:en-->', post_title) > 0 then SUBSTRING( post_title FROM 1 FOR LOCATE( '<!--:-->', post_title, LOCATE('<!--:en-->', post_title) ) - 1 ) else post_title end; ### strip before "en" content UPDATE wp_posts SET post_title = case when LOCATE('<!--:en-->', post_title) > 0 then SUBSTRING( post_title FROM LOCATE( '<!--:en-->', post_title ) + 10 ) else post_title end; ## `post_excerpt` ### strip after "en" content UPDATE wp_posts SET post_excerpt = case when LOCATE('<!--:en-->', post_excerpt) > 0 then SUBSTRING( post_excerpt FROM 1 FOR LOCATE( '<!--:-->', post_excerpt, LOCATE('<!--:en-->', post_excerpt) ) - 1 ) else post_excerpt end; ### strip before "en" content UPDATE wp_posts SET post_excerpt = case when LOCATE('<!--:en-->', post_excerpt) > 0 then SUBSTRING( post_excerpt FROM LOCATE( '<!--:en-->', post_excerpt ) + 10 ) else post_excerpt end; # combine the `tmp_excerpt` back into `post_content` and clean up ## concatenate `tmp_excerpt` and `post_content` back UPDATE wp_posts SET post_content = case when CHAR_LENGTH(tmp_excerpt) > 0 then CONCAT( tmp_excerpt, '<!--more-->', post_content ) else post_content end; ## drop the `tmp_excerpt` column ALTER TABLE `wp_posts` DROP `tmp_excerpt`;