CODEWITHSUNDEEP
sqlregexstringpostgresqlselect
Garfield
Garfield

Reputation: 143

Postgres: REGEXP_REPLACE using multiple columns

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') ?

Upvotes: 1

Views: 856

Answers (2)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

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

Upvotes: 1

GMB
GMB

Reputation: 222582

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

Upvotes: 3

Related Questions