How to remove the domain name from an email address in Oracle

Question

If input like this

as input- abcdxyz@gmail.com & pqrstuv@yahoo.com

and somebody want output like

as output- abcdxyz & pqrstuv

How can I do this using Oracle?

Answer 1

For your case this will do the job:

select regexp_replace('abcdxyz@gmail.com & pqrstuv@yahoo.com','@[a-zA-z0-9.]*','') from dual;

It is based on asumption that domain name contains only digits and a-z chars.

Answer 2

If you know all of the possible email addresses (not likely nor robust), you can use the TRIM function:

http://docs.oracle.com/javadb/10.8.3.0/ref/rreftrimfunc.html

More likely, you'll write your own that "rolls" the characters one at a time until it hits the delimiter. A shortish example to this is here (in the split function of the longer example in the accepted answer):

Is there a function to split a string in PL/SQL?

A more complex example and discussion on performance is here:

https://web.archive.org/web/20170914140540/https://stackoverflow.com/documentation/oracle/1968/splitting-delimited-strings#t=201709141405407224393