CODEWITHSUNDEEP
sqlregexoracle-database
CapitalBoo
CapitalBoo

Reputation: 113

Multiple Replacements with Oracle REGEXP_REPLACE

Ok so an Imperial Agent has gained access to all the Galactic mail servers and has created a mail account for Darth Vadar on each one...

There is a master distribution list maintained in an Oracle Column by the Republic that looks like this:

~To,Chewie,[email protected]~;~Cc,Han Solo,[email protected]~;~Cc,Luke Skywalker,[email protected]~

Our Imperial Agent needs help using Oracle REGEXP_REPLACE to replace all the email account names portions with the Darth Vadar account, Vadar@... so the end result would be:

~To,Chewie,[email protected]~;~Cc,Han Solo,[email protected]~;~Cc,Luke Skywalker,[email protected]~

Can this be done as a single statement? You would think that using the Dark Force would be easier then this.

Upvotes: 1

Views: 1747

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Might be too simple for some cases, but for your example this works:

regexp_replace(value, '[[:alnum:]\.]*@', 'Vadar@')

e.g:

select regexp_replace('~To,Chewie,[email protected]~;~Cc,Han Solo,[email protected]~;~Cc,Luke Skywalker,[email protected]~',
  '[[:alnum:].%_+-]*@', 'Vadar@')
from dual;


~To,Chewie,[email protected]~;~Cc,Han Solo,[email protected]~;~Cc,Luke Skywalker,[email protected]~

SQL Fiddle with dash and period examples.

Upvotes: 1

Related Questions