CODEWITHSUNDEEP
sqlpostgresqlsql-updatepattern-matching
user1298426
user1298426

Reputation: 3717

Use replace() function in an update to change more than one sub string of a column

Say I have student table with a column name.
This name column has values 'studentone', 'studenttwo', 'studentthree'
& I want to replace them with 'student1', 'student2', 'student3'.

For single replacement it's quite straight forward:

update student set name = replace(name, 'one', '1')

But what about multiple replacements? Any idea?

Upvotes: 7

Views: 14778

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657912

replace()

While you could pack all into one statement, it would be inefficient without a matching WHERE condition to exclude unaffected rows. It's expensive nonsense to update rows that don't actually change. See:

UPDATE student SET name = replace(name, 'one', '1') WHERE name LIKE '%one';
UPDATE student SET name = replace(name, 'two', '2') WHERE name LIKE '%two';
UPDATE student SET name = replace(name, 'three', '3') WHERE name LIKE '%three';

This only finds and updates rows that actually change. Matching the end of the string according to your example.
The most efficient way would be to combine that with something like @Bohemian suggested:

UPDATE student
SET    name = replace(replace(replace(
                      name
                    , 'one'  , '1')
                    , 'two'  , '2')
                    , 'three', '3')
WHERE  name ~ '(one|two|three)$';

But make sure that one replacement does not influence the next.

Actually, a couple of OR'ed LIKE expressions are typically faster than a single regular expression, so:

WHERE (name LIKE '%one' OR
       name LIKE '%two' OR
       name LIKE '%three');

CASE

Another possibility (as suggested in a comment):

SET    name = CASE WHEN name LIKE '%one'   THEN replace(name, 'one', '1')
                   WHEN name LIKE '%two'   THEN replace(name, 'two', '2')
                   WHEN name LIKE '%three' THEN replace(name, 'three', '3')
                   ELSE name
              END

The ELSE clause is just an optional insurance in this case. Since our WHERE clause excludes rows that wouldn't change, control never reaches ELSE. (Without spelling out ELSE, the expression would default to null in this case.)

Depending on exact circumstances, this might be optimized. For your example:

SET    name = CASE right(name, -7)
                 WHEN 'one'   THEN student || '1'
                 WHEN 'two'   THEN student || '2'
                 WHEN 'three' THEN student || '3'
                 ELSE name
              END

Upvotes: 4

Ben Wilson
Ben Wilson

Reputation: 2676

There is a prebuilt function here for replacing multiple strings in one go, using a JSON dictionary format for the input, e.g.:

select multi_replace('foo and bar is not foobar',
             '{"bar":"foo", "foo":"bar", "foobar":"foobar"}'::jsonb);

https://wiki.postgresql.org/wiki/Multi_Replace_plpgsql

Upvotes: 0

Bohemian
Bohemian

Reputation: 425198

I would just use multiple update statements, but if you absolutely must do it in one statement, just nest the replace calls:

update student set
  name = replace(replace(replace(name, 'one', '1'), 'two', '2'), 'three', '3')

This works because (although inefficient) calls to replace() have no effect if the search term is not found.

Upvotes: 7

Related Questions