CODEWITHSUNDEEP
stringpostgresqlreplace
rshar
rshar

Reputation: 1477

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

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

Upvotes: 0

Views: 229

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions