OraFAQ Forum: SQL & PL/SQL » how to seperate a sentance into array by a seperator ';' using pl/sql ?

Home » SQL & PL/SQL » SQL & PL/SQL » how to seperate a sentance into array by a seperator ';' using pl/sql ?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to seperate a sentance into array by a seperator ';' using pl/sql ? [message #308273]

Sun, 23 March 2008 06:52

fazal_haq2001
Messages: 35
Registered: July 2006
Location: India

Member

Hi all,
how to seperate the sentance into an array by a seperator using pl/sql ?

E.x ,
the data is like
rate_f1
1500;25000;100;200
i o/p will be

rate_f1
1500
25000
100
200
the single row should be splitted into 5 rows

please assist..

Regards,
Faz..

Report message to a moderator

Re: how to seperate a sentance into array by a seperator ';' using pl/sql ? [message #308275 is a reply to message #308273]

Sun, 23 March 2008 07:28

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

See Tom Kyte's in_list function in the following thread:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

Report message to a moderator

Re: how to seperate a sentance into array by a seperator ';' using pl/sql ? [message #308276 is a reply to message #308275]

Sun, 23 March 2008 08:06

fazal_haq2001
Messages: 35
Registered: July 2006
Location: India

Member

thanks a lot,,
it is working ...

i got it through other way also..

declare
wrd varchar2(1000) := '15000;150;25000;100;7500' ;
act varchar2(20) ;
n number ;
begin
wrd := wrd||';' ;
loop
n:= instr(wrd,';') ;
act := substr(wrd,1,n-1) ;
exit when act is null;
insert into tmp values(act) ;
wrd := substr(wrd, n+1 ) ;
end loop ;
commit ;
end ;

Report message to a moderator

Re: how to seperate a sentance into array by a seperator ';' using pl/sql ? [message #308282 is a reply to message #308273]

Sun, 23 March 2008 11:38

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It has already said to you to ead OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Previous Topic:	select statement options
Next Topic:	SQL Query to create a report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 12:09:48 CST 2024