Replace strings in a comma separated values of multiple strings in PostgreSQL 11.0

Question

I have following table in PostgreSQL 11.0

forms
ampoules, capsules, coated tablets, infusion ampoules, liquids, powders, tablets
capsules, coated tablets, powders, tablets, unspecified
coated tablets

I would like to replace certain strings in this comma separated values for each row and get distinct values in the end. For eg. coated tablets by tablets, capsules by tablets, infusion ampoules by ampoules.

The desired output is:

forms_normalized
ampoules, liquids, powders, tablets
tablets, powders, unspecified
tablets

I have no starting point to resolve this issue. Any help is highly appreciated.

Thanks

user330315 · Accepted Answer · 2021-05-12 14:05:19Z

1

One way to do it, is to expand the comma separated list to a "table", then aggregate the distinct values back and replace the ones you want.

select d.id, 
       (select string_agg(distinct case t.form
                             when 'coated tablets' then 'tablets'
                             when 'infusion ampoules' then 'ampoules'
                             else t.form
                           end, ',')
        from regexp_split_to_table(d.forms, '\s*,\s*') as t(form))
from data d

answered May 12, 2021 at 14:05

user330315

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

2 Comments

rshar Over a year ago

It worked perfectly fine. Thanks !! How can I use multiple strings to be replaced? For eg. coated tablets by tablets, capsules by tablets. I modified the input for clarity.

Belayer Over a year ago

Just add to the when conditions. ".., when 'coated tablets' then 'tablets' when 'capsules' then 'tablets' when ... . The problem arises not that I have multiple items which are different things, identified the same, and I do not know which is which. If the combination is the goal, then fine, but be careful when combine different things to the same.

Collectives™ on Stack Overflow

Replace strings in a comma separated values of multiple strings in PostgreSQL 11.0

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