using sql regrex_replace to replace every word that starts with a # symbol with mask(word)

Question

I have text data as following in single column

This is a credit card number #341332123-432432-413213-13223. The CC # should be masked
This is a email #[email protected] The email address should be masked
This is a phone #555-555-5555, The phone number should be masked

I would like to replace the #{token} with mask(token)

e.g.

This is a credit card number mask(341332123-432432-413213-13223). The CC # should be masked
This is a email mask([email protected]) The email address should be masked
This is a phone mask(555-555-5555), The phone number should be masked

Note. This is just text manipulation. I am not doing any masking in this step.

Answer 1

You can use regexp_replace:

select regexp_replace(col_text, '(#)((?:[\d-]+)|(?:\w+@\w+\.\w+))', 'mask(\2)') from tbl

See fiddle.