|
|
Re: Reading values from a delimited line [message #334828 is a reply to message #334826] |
Fri, 18 July 2008 04:47   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you're using 10g, here's a way of doing it:select regexp_substr(';'||replace(x,';',';;')||';',';[^;]+;',1,lvl)
from (select x, level lvl
from (select 'bob@xxx.co.uk;test@test.co.uk;aaaa@aaa.co.uk;bbbb.bbb@co.uk' x
from dual)
connect by level <=length(x) - length(replace(x,';',''))+1);
The key to this is this:
1) Make sure every email address is wrapped in it's own set of ';'
2) Use a Regular expression to extract an email address from this string (regexp_substr(x,';[^;]+;',1,lvl) will get a string that starts with a ';',has one or more non ';'and then another ';'
3) Use a row-generator to let you use this reg-exp several times, and get all of the matching strings out of the regexp.
|
|
|
Re: Reading values from a delimited line [message #334871 is a reply to message #334817] |
Fri, 18 July 2008 08:29   |
rjsha1
Messages: 22 Registered: December 2005 Location: Birmingham England
|
Junior Member |
|
|
Michel - thanks that was the one thing I never thought of searching upon - my apolgies. sorry and thank you.
JRowbottom - what a brilliant solution - exactly what i was looking for - Thank you very much for your time and assistance.
Regards ob.
|
|
|
Re: Reading values from a delimited line [message #334965 is a reply to message #334828] |
Fri, 18 July 2008 22:35   |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
JRowbottom, looks like the semicolons persist.
SQL> select regexp_substr(';'||replace(x,';',';;')||';',';[^;]+;',1,lvl)
2 from (select x, level lvl
3 from (select 'bob@xxx.co.uk;test@test.co.uk;aaaa@aaa.co.uk;bbbb.bbb@co.uk' x
4 from dual)
5 connect by level <=length(x) - length(replace(x,';',''))+1);
REGEXP_SUBSTR(';'||REPLACE(X,';',';;')||';',';[^;]+;',1,LVL)
--------------------------------------------------------------------------------
;bob@xxx.co.uk;
;test@test.co.uk;
;aaaa@aaa.co.uk;
;bbbb.bbb@co.uk;
should be able to rewrite as follows:
SQL> select regexp_substr(x,'[^;]+',1,lvl)
2 from (select x, level lvl
3 from (select 'bob@xxx.co.uk;test@test.co.uk;aaaa@aaa.co.uk;bbbb.bbb@co.uk' x
4 from dual)
5 connect by level <=length(x) - length(replace(x,';',''))+1);
REGEXP_SUBSTR(X,'[^;]+',1,LVL)
-----------------------------------------------------------
bob@xxx.co.uk
test@test.co.uk
aaaa@aaa.co.uk
bbbb.bbb@co.uk
|
|
|
|