philologist's techblog

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.