Home » SQL & PL/SQL » SQL & PL/SQL » get a text according to a delimeter
get a text according to a delimeter [message #211029] Mon, 25 December 2006 08:34 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

hii all

i have a line contain several word that are splitted by a deleimeter which is ';'

suppose the line word is
1;emad;aast;23


i want to split each word by using an sql statment



thanks for everyone helped and helping me

Re: get a text according to a delimeter [message #211033 is a reply to message #211029] Mon, 25 December 2006 10:19 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>thanks for everyone helped and helping me

The homework tutor is down the hall at the 2nd door on the right.

Do not cross/multi-post
Re: get a text according to a delimeter [message #211038 is a reply to message #211029] Mon, 25 December 2006 12:43 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


curamgph>create table foo (id number ,line varchar2(200));

insert into foo values (1,'He ; is;a jolly;good fellow')

insert into foo values (2,'13;emad;aast;23');

commit;


select id ,spl from foo
model
main grr
partition by (id)
dimension by (0 as dmn)
measures (cast (null as varchar2(4000)) as spl ,
1 as pos ,lne)
ignore nav rules sequential order iterate (4000)
until (pos[iteration_number] =0 )
(
pos[iteration_number]=instr(lne[0],';', case when
iteration_number=0 then 1 else pos[cv()-1]+1 end),
spl[iteration_number]=substr(lne[0],
case when iteration_number=0 then 1
else pos[cv()-1]+1 end ,
case when pos[cv()]=0 then length (lne[0])
else pos[cv()]-pos[cv()-1]-1 end )
)
curamgph>/

ID SPL
-- ---------------
1 He
1 is
1 a jolly
1 good fellow
2 13
2 emad
2 aast
2 23

8 rows selected.

curamgph>
Re: get a text according to a delimeter [message #211076 is a reply to message #211038] Tue, 26 December 2006 02:27 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thanks to a typo, I managed to understand it much easier Very Happy

This line
1 as pos ,lne)
should read
1 as pos ,line as lne)

Please keep them model clauses coming, srinivnp!

(btw, please read the second topic in the newbie forum to see how to format your posts to make them more readable)
Previous Topic: Deterministic Function
Next Topic: sql*plus
Goto Forum:
  


Current Time: Sat Dec 03 09:42:38 CST 2016

Total time taken to generate the page: 0.06729 seconds