Extract mail addresses after text-CodePudding

I have a stored procedure in Oracle which takes a text as input. That text includes information about an e-mail, containing to-addresses, subject, body and so on. The text looks like this:

MESSAGE:
TO: john.doe@world.com
TO: jane.doe@world.com
FROM: joe.schmoe@world.com
SUBJECT: IMPORTANT MESSAGE
CONTENT:
This is an important message.

What is the easiest way to extract the mail addresses after TO: in an oracle stored procedure?

CodePudding user response：

If data you posted is contained in the same variable (or passed via procedure's parameter), then you could use regular expressions:

SQL> with test (col) as (select
  2  'MESSAGE:
  3  TO: [email protected]
  4  TO: [email protected]
  5  FROM: [email protected]
  6  SUBJECT: IMPORTANT MESSAGE
  7  CONTENT:
  8  This is an important message.'
  9  from dual
 10  )
 11  select replace(regexp_substr(col, 'TO: . ', 1, level), 'TO: ', '') result
 12  from test
 13  connect by level <= regexp_count(col, 'TO:');

RESULT
--------------------------------------------------------------------------------
john.doe@world.com
jane.doe@world.com

SQL>