Postgres: REGEXP_REPLACE using multiple columns

Question

I have a table like this:

Address                      | Remove1 | Remove2 | Remove3
-------------------------------------------------------------
100 Street, City, State Zip  | Street  |  City   | State

I want output like this:

Address                      | Remove1 | Remove2 | Remove3 | OutputCol
------------------------------------------------------------------------------
100 Street, City, State Zip  | Street  |  City   | State   | 100 *, *, * Zip

Basically I want to replace the text in Address Column matching on multiple remove columns with '*'

I have tried using REGEXP_REPLACE and REPLACE functions but both of them are giving partial results. Is there a way I can use the REGEXP_REPLACE(Address, (Remove1|Remove2|Remove3) , '*', 'g') ?

GMB · Accepted Answer · 2020-04-29 17:49:59Z

3

I think that you are quite close. You just need to properly concatenate the column values, like so:

regexp_replace(
    address,
    concat_ws('|', remove1, remove2, remove3),
    '*',
    'g'
)

Demo on DB Fiddle:

select  
    t.*,
    regexp_replace(address, concat_ws('|', remove1, remove2, remove3), '*', 'g') OuputCol 
from mytable t

address                     | remove1 | remove2 | remove3 | ouputcol       
:-------------------------- | :------ | :------ | :------ | :--------------
100 Street, City, State Zip | Street  | City    | State   | 100 *, *, * Zip

answered Apr 29, 2020 at 17:49

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Derviş Kayımbaşıoğlu · Accepted Answer · 2020-04-29 17:55:28Z

1

This is same as what you did. You just need string concatenation

with wt as 
(
   select '100 Street, City, State Zip' address, 
     'Street' Remove1, 'City' Remove2, 'State' Remove3
)
select REGEXP_REPLACE(address, '(' || Remove1 || '|' || Remove2 ||'|'||Remove3 ||')' , '*', 'g') from wt

Demo

answered Apr 29, 2020 at 17:55

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Collectives™ on Stack Overflow

Postgres: REGEXP_REPLACE using multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related