{"id":764,"date":"2005-08-04T23:13:00","date_gmt":"2005-08-05T04:13:00","guid":{"rendered":"http:\/\/blogs.terrorware.com\/geoff\/2005\/08\/04\/howto-replacing-a-substring-in-a-column-value-with-a-mysql-query\/"},"modified":"2005-08-04T23:13:00","modified_gmt":"2005-08-05T04:13:00","slug":"howto-replacing-a-substring-in-a-column-value-with-a-mysql-query","status":"publish","type":"post","link":"http:\/\/blogs.terrorware.com\/geoff\/2005\/08\/04\/howto-replacing-a-substring-in-a-column-value-with-a-mysql-query\/","title":{"rendered":"howto: replacing a substring in a column value with a MySQL query"},"content":{"rendered":"

MySQL Reference Manual :: 13.2.10 UPDATE Syntax<\/a>: “You sometimes run into the problem that you want to replace a substring occuring in a column with a different string, without touching the rest of the string. The solution is surprisingly simple, thanks to MySQL:<\/p>\n

UPDATE xoops_bb_posts_text
\nSET post_text=(
\nREPLACE (post_text,
\n‘morphix.sourceforge.net’,
\n‘www.morphix.org’));<\/p>\n

using the string function REPLACE, all items in the post_text column with ‘morphix.sourceforge.net’ get this substring replaced by ‘www.morphix.org’. Ideal when writing a script is just too much effort.”<\/p>\n

This will be useful for changing a bunch of text on the various wiki pages for pixfestdoc.terrorware.com<\/a>. I had a bunch of links that read ‘View\/Upload Photos’ and I want to change them to ‘View\/Upload Photos and Video’. This would have sucked to do through a web interface, but messing with the database should make it quick. I ran into a little problem with the Mediawiki handles caching so the changes didn’t show up right away, but I waited overnight and it worked out.<\/p>\n

UPDATE: This the query that I used to add additional text to all the show pages:<\/p>\n

update pixfestmw_cur set cur_text=(concat(‘== Photos\/Video ==\\n\\n’, concat(cur_text, ‘\\n\\n== Audio ==\\n\\n== Stories ==\\n\\nTo read stories from this show, please click on the \\’discussion\\’ tab at the top of this page.\\n\\n== External Links ==’))) where cur_title like ‘%June_%’;<\/p>\n","protected":false},"excerpt":{"rendered":"

MySQL Reference Manual :: 13.2.10 UPDATE Syntax: “You sometimes run into the problem that you want to replace a substring occuring in a column with a different string, without touching the rest of the string. The solution is surprisingly simple, thanks to MySQL: UPDATE xoops_bb_posts_text SET post_text=( REPLACE (post_text, ‘morphix.sourceforge.net’, ‘www.morphix.org’)); using the string function… Continue reading howto: replacing a substring in a column value with a MySQL query<\/span><\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[1],"tags":[],"class_list":["post-764","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4wnIz-ck","_links":{"self":[{"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts\/764","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/comments?post=764"}],"version-history":[{"count":0,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts\/764\/revisions"}],"wp:attachment":[{"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/media?parent=764"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/categories?post=764"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/tags?post=764"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}