Home » SQL & PL/SQL » SQL & PL/SQL » how to seperate a sentance into array by a seperator ';' using pl/sql ?
how to seperate a sentance into array by a seperator ';' using pl/sql ? [message #308273] Sun, 23 March 2008 06:52 Go to next message
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..
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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
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 Go to previous messageGo to next message
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 ;


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 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: select statement options
Next Topic: SQL Query to create a report
Goto Forum:
  


Current Time: Wed Dec 07 10:39:24 CST 2016

Total time taken to generate the page: 0.16665 seconds