Multiple Replacements with Oracle REGEXP_REPLACE

Question

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.

Alex Poole · Accepted Answer · 2014-03-25 08:07:58Z

1

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.

edited Mar 25, 2014 at 8:07

answered Mar 24, 2014 at 22:32

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

2 Comments

CapitalBoo Over a year ago

If we add Obi-Wan to the mix we have trouble.

~To,Chewie,[email protected]~;~Cc,Han Solo,[email protected]~;~Cc,Luke Skywalker,[email protected]~;~Cc,Obi-Wan Kenobi,[email protected]~

Alex Poole Over a year ago

@CapitalBoo - said it might be too simple. I'd already allowed for periods, but now expanded the pattern to include other valid characters. This may still not be everything allowed by RFC 5322, and only covers English, but not sure what scope you're dealing with.

Collectives™ on Stack Overflow

Multiple Replacements with Oracle REGEXP_REPLACE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related