{"id":12501,"date":"2025-04-30T01:03:16","date_gmt":"2025-04-29T20:03:16","guid":{"rendered":"https:\/\/alexeyka.zantsev.com\/?p=12501"},"modified":"2025-04-30T01:05:18","modified_gmt":"2025-04-29T20:05:18","slug":"sql-sed-like-replacement-of-a-string","status":"publish","type":"post","link":"https:\/\/alexeyka.zantsev.com\/?p=12501","title":{"rendered":"SQL: sed-like replacement of a string"},"content":{"rendered":"\n<p>Imagine the situation: you have an SQL table with some string in some column. There is a plenty of such rows, and you want to replace this string in each row with some other string, something like in Bash &#8211; sed &#8216;s\/green apple\/blue apple\/g&#8217;.<\/p>\n\n\n\n<p>The original table looks like this.<\/p>\n\n\n\n<p>Select:<\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-red-color has-text-color has-link-color wp-elements-8de155840de96f74a258ad04256aa293\"><code>SELECT\u00a0\"prefix\",\u00a0\"description\",\u00a0\"ruleid\"\u00a0\nFROM\u00a0\"dr_rules\"\u00a0\nWHERE\u00a0\"description\"\u00a0LIKE\u00a0'%from ACME%'\u00a0\nORDER BY\u00a0\"ruleid\"\u00a0\nDESC LIMIT\u00a05;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted has-black-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-d2dc7b94ea40595c5f1ff71ad744abb4\">prefix\t        description<br>73843444445\tNovokuznetsk from ACME<br>73843208507\tNovokuznetsk from ACME<br>73843400000\tMaxim Novokuznetsk  from ACME<br>73843208505\tOffice Poehali Novokuznetsk  from ACME<br>73843208508\tOffice Maxim Novokuznetsk  from ACME<\/pre>\n\n\n\n<p>We want to change &#8220;from ACME&#8221; to &#8220;moved from ACME&#8221;.<\/p>\n\n\n\n<p>First, let&#8217;s test our SQL statement with SELECT, no changes will be made:<\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-red-color has-text-color has-link-color wp-elements-86d38fe5a4b185722188f40417d8f15d\"><code>SELECT REPLACE(description,'from ACME','moved from ACME') \nFROM dr_rules\nWHERE description LIKE('%from ACME%')\nAND description NOT LIKE('%moved from ACME');<\/code><\/pre>\n\n\n\n<p>Now do an SQL UPDATE in a sed-like style:<\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-red-color has-text-color has-link-color wp-elements-d9636f85f1845b5b0ff3ca1b53cc997e\"><code>UPDATE dr_rules \nSET description=REPLACE(description,'from ACME','moved from ACME')\nWHERE description LIKE('%from ACME%')\nAND description NOT LIKE('%moved from ACME');<\/code><\/pre>\n\n\n\n<p>The result after an UPDATE:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted has-black-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-321a7d85b3891e007d32e69af4c1a827\">prefix\t        description<br>73843444445\tNovokuznetsk moved from ACME<br>73843208507\tNovokuznetsk moved from ACME<br>73843400000\tMaxim Novokuznetsk  moved from ACME<br>73843208505\tOffice Poehali Novokuznetsk  moved from ACME<br>73843208508\tOffice Maxim Novokuznetsk  moved from ACME<\/pre>\n\n\n\n<p>So, to test the statement, do:<\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-red-color has-text-color has-link-color wp-elements-f1395a0ec4887b9ace4ed9e902cfecbe\"><code>SELECT REPLACE(columnname,'old string','new string') \nFROM tablename\nWHERE columnname LIKE('%old string%')\nAND columnname NOT LIKE('%new string');<\/code><\/pre>\n\n\n\n<p>To replace, do:<\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-red-color has-text-color has-link-color wp-elements-5fec501922677e8b6893814c25fb38ab\"><code>UPDATE tablename \nSET columnname=REPLACE(columnname,'old string','new string')\nWHERE columnname LIKE('%old string%')\nAND columnname NOT LIKE('%new string');<\/code><\/pre>\n\n\n\n<p>If you need case-insensitive, use ILIKE instead of LIKE.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Imagine the situation: you have an SQL table with some string in some column. There is a plenty of such rows, and you want to replace this string in each row with some other string, something like in Bash &#8211; sed &#8216;s\/green apple\/blue apple\/g&#8217;. The original table looks like this. Select: Result: prefix description73843444445 Novokuznetsk [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[209,93],"class_list":["post-12501","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-sed","tag-sql"],"_links":{"self":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts\/12501","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12501"}],"version-history":[{"count":29,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts\/12501\/revisions"}],"predecessor-version":[{"id":12560,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts\/12501\/revisions\/12560"}],"wp:attachment":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12501"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12501"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12501"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}