{"id":5295,"date":"2018-06-07T16:46:39","date_gmt":"2018-06-07T14:46:39","guid":{"rendered":"https:\/\/florianbrinkmann.com\/en\/?p=5295"},"modified":"2020-02-09T11:21:44","modified_gmt":"2020-02-09T10:21:44","slug":"search-replace-mysql","status":"publish","type":"post","link":"https:\/\/florianbrinkmann.com\/en\/search-replace-mysql-5295\/","title":{"rendered":"Search and replace in MySQL"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Currently, I want to wrap all acronyms \u2013 \u00bbPHP\u00ab, for example \u2013 in a <code class=\"lang-markup\">span<\/code> element to display them as small capitals. So I searched for a way to search and replace in MySQL, and there it is: <code class=\"lang-sql\">REGEXP_REPLACE<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example, the following command wraps <code class=\"lang-markup\">WOFF<\/code> in the post titles in the wanted markup (it is a good idea to create a backup before):<\/p>\n\n\n<pre class=\"wp-block-code lang-sql\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-string\">`wp_posts`<\/span> <span class=\"hljs-keyword\">SET<\/span> <span class=\"hljs-string\">`post_title`<\/span> = REGEXP_REPLACE(post_title <span class=\"hljs-keyword\">COLLATE<\/span> utf8mb4_bin, <span class=\"hljs-string\">'WOFF'<\/span>, <span class=\"hljs-string\">'&lt;span class=\"smcp\"&gt;WOFF&lt;\/span&gt;'<\/span> ) <span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-string\">`ID`<\/span> = <span class=\"hljs-number\">4691<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p class=\"wp-block-paragraph\">The first param of <code class=\"lang-sql\">REGEXP_REPLACE<\/code> is the string to search in \u2013 we set the column\u2019s name we want to search in. The second parameter is the searched string and the third the string to replace the searched string with. Setting <code class=\"lang-sql\">COLLATE utf8mb4_bin<\/code> (you may need to adjust the collation if you use another one) is important because otherwise, the search would be case insensitive.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/mariadb.com\/kb\/en\/library\/regexp_replace\/\">Like written in the MariaDB documentation<\/a> (and as the name suggests) you can also use regular expressions with <code class=\"lang-sql\">REGEXP_REPLACE<\/code>. For my German version of the weekly recap, I used the following:<\/p>\n\n\n<pre class=\"wp-block-code lang-sql\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-string\">`wp_posts`<\/span> <span class=\"hljs-keyword\">SET<\/span> <span class=\"hljs-string\">`post_title`<\/span> = REGEXP_REPLACE(post_title <span class=\"hljs-keyword\">COLLATE<\/span> utf8mb4_bin, <span class=\"hljs-string\">'KW(&#91;0-9]+)'<\/span>, <span class=\"hljs-string\">'&lt;span class=\"smcp\"&gt;KW&lt;\/span&gt;\\\\1'<\/span> ) <span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-string\">`ID`<\/span> = <span class=\"hljs-number\">4691<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p class=\"wp-block-paragraph\"><code class=\"lang-sql\"><span class=\"smcp\">\\\\1<\/span><\/code>&nbsp;inserts the part inside the brackets from the searched string into the replace string: the week number.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Currently, I want to wrap all acronyms \u2013 \u00bbPHP\u00ab, for example \u2013 in a span element to display them as small capitals. So I searched for a way to search and replace in MySQL, and there it is: REGEXP_REPLACE. For example, the following command wraps WOFF in the post titles in the wanted markup (it [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"wpf_show_in_dewp_planet_feed":false,"flobn_post_versions":"","webmentions_disabled_pings":false,"webmentions_disabled":false,"lazy_load_responsive_images_disabled":false,"footnotes":""},"categories":[6],"tags":[],"class_list":["post-5295","post","type-post","status-publish","format-standard","hentry","category-web-development"],"wp-worthy-pixel":{"ignored":false,"public":null,"server":null,"url":null},"wp-worthy-type":"normal","_links":{"self":[{"href":"https:\/\/florianbrinkmann.com\/en\/wp-json\/wp\/v2\/posts\/5295","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/florianbrinkmann.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/florianbrinkmann.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/florianbrinkmann.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/florianbrinkmann.com\/en\/wp-json\/wp\/v2\/comments?post=5295"}],"version-history":[{"count":4,"href":"https:\/\/florianbrinkmann.com\/en\/wp-json\/wp\/v2\/posts\/5295\/revisions"}],"predecessor-version":[{"id":5962,"href":"https:\/\/florianbrinkmann.com\/en\/wp-json\/wp\/v2\/posts\/5295\/revisions\/5962"}],"wp:attachment":[{"href":"https:\/\/florianbrinkmann.com\/en\/wp-json\/wp\/v2\/media?parent=5295"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/florianbrinkmann.com\/en\/wp-json\/wp\/v2\/categories?post=5295"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/florianbrinkmann.com\/en\/wp-json\/wp\/v2\/tags?post=5295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}