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" {[email protected]};"User_2" {[email protected]};"User_3" {[email protected]};"User_4" {[email protected]};
select SUBSTR(string ,INSTR(string ,'<',-1,2)) EMAIL
from dual;
What I need is something like this:
[email protected];[email protected];[email protected];[email protected];
Looking for the preceding { and the following }; which seems to appear in your string you can use:
SELECT REGEXP_REPLACE(
'"User_1" {[email protected]};"User_2" {[email protected]};"User_3" {[email protected]};"User_4" {[email protected]};',
'.*?\{(.*?)\};',
'\1;'
) AS emails
FROM DUAL;
Output:
EMAILS
------------------------------------------------------------------------
[email protected];[email protected];[email protected];[email protected];
{ 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.VARRAY data type? Or do you mean something else?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" {[email protected]};"User_2" {[email protected]};"User_3" {[email protected]};"User_4" {[email protected]};'
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 [email protected]
2 [email protected]
3 [email protected]
4 [email protected]
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
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.]+
ORA-12728: invalid range in regular expression.
{ }) 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.