Home » SQL & PL/SQL » SQL & PL/SQL » Reading values from a delimited line (Oracle 10g)
Reading values from a delimited line [message #334817] Fri, 18 July 2008 03:55 Go to next message
rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England
Junior Member
Hi there,
Can some one point me in the right direction, with this problem.

I have a field with email addresses delimited by ';'
e.g.
bob@xxx.co.uk;test@test.co.uk;aaaa@aaa.co.uk;bbbb.bbb@co.uk <EOL>

Is it possible to do this in sql*plus and get the result

bob@xxx.co.uk
test@test.co.uk
aaaa@aaa.co.uk
bbbb.bbb@co.uk

I have been using instr and substr and can get the first or the last email address.

If it is not possible i'll use PL/SQL.
My apologies if this has been posted before but I have searchad the various forums.

Thanks

Re: Reading values from a delimited line [message #334826 is a reply to message #334817] Fri, 18 July 2008 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
My apologies if this has been posted before but I have searchad the various forums

Yes, it has been asked many times.
Here's an example:
http://www.orafaq.com/forum/m/255052/102589/#msg_255052

Regards
Michel
Re: Reading values from a delimited line [message #334828 is a reply to message #334826] Fri, 18 July 2008 04:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Reading values from a delimited line [message #335146 is a reply to message #334965] Mon, 21 July 2008 03:53 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Nice - I didn't think of doing that. (obviously...).
It saves having a leading and trailing delimiter too.

Thanks.
Previous Topic: Convert Number to hours or minutes
Next Topic: Problem in joining 2 queries
Goto Forum:
  


Current Time: Thu Feb 13 23:20:04 CST 2025