CODEWITHSUNDEEP
oracle-database
Rune Kasseth
Rune Kasseth

Reputation: 3

Extract mail addresses after text

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: [email protected]
TO: [email protected]
FROM: [email protected]
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?

Upvotes: 0

Views: 35

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

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
--------------------------------------------------------------------------------
[email protected]
[email protected]

SQL>

Upvotes: 1

Related Questions