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 – sed ‘s/green apple/blue apple/g’.
The original table looks like this.
Select:
SELECT "prefix", "description", "ruleid"
FROM "dr_rules"
WHERE "description" LIKE '%from ACME%'
ORDER BY "ruleid"
DESC LIMIT 5;Result:
prefix description
73843444445 Novokuznetsk from ACME
73843208507 Novokuznetsk from ACME
73843400000 Maxim Novokuznetsk from ACME
73843208505 Office Poehali Novokuznetsk from ACME
73843208508 Office Maxim Novokuznetsk from ACME
We want to change “from ACME” to “moved from ACME”.
First, let’s test our SQL statement with SELECT, no changes will be made:
SELECT REPLACE(description,'from ACME','moved from ACME')
FROM dr_rules
WHERE description LIKE('%from ACME%')
AND description NOT LIKE('%moved from ACME');Now do an SQL UPDATE in a sed-like style:
UPDATE dr_rules
SET description=REPLACE(description,'from ACME','moved from ACME')
WHERE description LIKE('%from ACME%')
AND description NOT LIKE('%moved from ACME');The result after an UPDATE:
prefix description
73843444445 Novokuznetsk moved from ACME
73843208507 Novokuznetsk moved from ACME
73843400000 Maxim Novokuznetsk moved from ACME
73843208505 Office Poehali Novokuznetsk moved from ACME
73843208508 Office Maxim Novokuznetsk moved from ACME
So, to test the statement, do:
SELECT REPLACE(columnname,'old string','new string')
FROM tablename
WHERE columnname LIKE('%old string%')
AND columnname NOT LIKE('%new string');To replace, do:
UPDATE tablename
SET columnname=REPLACE(columnname,'old string','new string')
WHERE columnname LIKE('%old string%')
AND columnname NOT LIKE('%new string');If you need case-insensitive, use ILIKE instead of LIKE.