CODEWITHSUNDEEP
arrayspostgresqlreplace
Daniel
Daniel

Reputation: 47

Postgres replace function with an array of values

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'

Upvotes: 2

Views: 1555

Answers (1)

klin
klin

Reputation: 121634

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.

Upvotes: 3

Related Questions