Postgres replace function with an array of values

Question

Is there a way to use an input array of values as a way to replace different string occurrences in one go?

Say we have an input array of ['a', 'b', 'c', 'd'], and a replacement array of ['1', '2', '3', '4']. Now if we were to do something like:

UPDATE table SET field=replace('a string to be replaced', input, replacement)

every a would get replaced with whatever is on the same index of replacement ('1'), every b replaced with the same index of replacement ('2'), etc, so field would then become '1 string to 2e repl12e4'.

(Using Postgres 9.4)

EDIT:

Another example, input is ['string', 'replaced'], replacement is ['cat', 'dog'], field would become 'a cat to be dog'

postgresql.org/docs/14/functions-array.html array_replace() — wildplasser
– wildplasser, Commented May 26, 2022 at 12:12

klin · Accepted Answer · 2022-05-26 18:42:00Z

3

A natural approach to this type of tasks is to use a loop inside a custom function:

create or replace function multireplace_loop
    (string text, pattern text[], replacement text[])
returns text language plpgsql immutable as $$
declare
    rec record;
begin
    for rec in
        select ord, old_str, new_str
        from unnest(pattern, replacement) 
        with ordinality as o(old_str, new_str, ord)
        order by ord
    loop
        string:= replace(string, rec.old_str, rec.new_str);
    end loop;
    return string;
end $$;

select multireplace_loop
    ('a string to be replaced', '{string, replaced}', '{cat, dog}');

There is an elegant pure SQL alternative solution applying recursive query:

create or replace function multireplace_recursive
    (string text, pattern text[], replacement text[])
returns text language sql immutable as $$
    with recursive input_rows as (
        select ord, old_str, new_str
        from unnest(pattern, replacement) 
        with ordinality as o(old_str, new_str, ord)
    ),
    recursive_replace as (
        select ord, replace(string, old_str, new_str) as string
        from input_rows
        where ord = 1
    union all
        select i.ord, replace(string, i.old_str, i.new_str)
        from recursive_replace r
        join input_rows i on i.ord = r.ord+ 1
    )
    select string
    from recursive_replace
    order by ord desc
    limit 1
$$;

select multireplace_recursive
    ('a string to be replaced', '{string, replaced}', '{cat, dog}');

Test it in Db<>Fiddle.

edited May 26, 2022 at 18:42

answered May 26, 2022 at 12:35

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Over a year ago

what if it's an input array with n-length strings?

klin Over a year ago

I'm glad it helped. Note, that while the recursive query may be more pleasant, the iteration approach should be significantly faster.

Collectives™ on Stack Overflow

Postgres replace function with an array of values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related