1

I need to extract all e-mail accounts mentioned on a single string, so far I've tried with SUBSTR and INSTR, but with no success, here an example:

The string looks like this:

()
string = "User_1" {user_1@domain.com};"User_2" {user_2@domain.com};"User_3" {user_3@domain.com};"User_4" {user_4@domain.com};


select SUBSTR(string ,INSTR(string ,'<',-1,2)) EMAIL
from dual;

What I need is something like this:

user_1@domain.com;user_2@domain.com;user_3@domain.com;user_4@domain.com;
MT0
  • 143,790
  • 11
  • 59
  • 117
ocean_blue
  • 11
  • 2
  • 5
  • So... are all emails enclosed in curly braces (`{ }`) and the only things ever enclosed in curly braces are email addresses? If so, then the problem is not too complicated. Also: what is the desired output? You show a single string, separated by semicolons. That doesn't seem optimal; the best output is one email per row of output. –  Mar 29 '17 at 22:59

3 Answers3

1

Looking for the preceding { and the following }; which seems to appear in your string you can use:

SELECT REGEXP_REPLACE(
         '"User_1" {user_1@domain.com};"User_2" {user_2@domain.com};"User_3" {user_3@domain.com};"User_4" {user_4@domain.com};',
         '.*?\{(.*?)\};',
         '\1;'
       ) AS emails
FROM   DUAL;

Output:

EMAILS                                                                
------------------------------------------------------------------------
user_1@domain.com;user_2@domain.com;user_3@domain.com;user_4@domain.com;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thank you, what if there are no numbers involved in the main string and instead regular names? – ocean_blue Mar 29 '17 at 23:54
  • The regular expression only matches `{` and `}` and gets the e-mail from between those characters; it does not match any numbers. The `\1` in the replacement string is a reference to the first capture group which is where the e-mail is matched. – MT0 Mar 30 '17 at 08:33
  • my bad, I made a mistake when implementing the actual usernames and emails. Thank you!!! – ocean_blue Mar 30 '17 at 14:50
  • Can I pass the string result to an array? – ocean_blue Mar 30 '17 at 18:32
  • @ocean_blue Do you mean a collection data type or a `VARRAY` data type? Or do you mean something else? – MT0 Mar 31 '17 at 01:04
0

From this site I found this nasty bit of regex:

(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])

But, it seems to work! I cannot break down what it's doing for you though :P

Here's an example on regexr

EDIT: That is giving errors in Oracle, so I've written up a new one:

([a-zA-Z0-9.\-_])+\@[a-zA-Z]+\.[a-zA-Z.]+

Here's an example of the new one in regexr

Aaron N. Brock
  • 4,276
  • 2
  • 25
  • 43
0

Something like this will work for a single input string. It can be adapted to work with more than one input string.

with
     inputs (str) as (
       select '"User_1" {user_1@domain.com};"User_2" {user_2@domain.com};"User_3" {user_3@domain.com};"User_4" {user_4@domain.com};'
       from   dual
     )
select level as ord,
       substr(str, instr(str, '{', 1, level) + 1,
              instr(str, '}', 1, level) - instr(str, '{', 1, level) - 1) as email
from   inputs
connect by level <= length(str) - length(replace(str, '{'))
;

ORD  EMAIL
---  -----------------
  1  user_1@domain.com
  2  user_2@domain.com
  3  user_3@domain.com
  4  user_4@domain.com