Home » SQL & PL/SQL » SQL & PL/SQL » Comma seperated value (Oracle 10g)
Comma seperated value [message #322062] Thu, 22 May 2008 05:30 Go to next message
someswar1
Messages: 53
Registered: January 2008
Member
Hi,
Can any one tell me the sugession about Assume i have table columm name is sud and the value is like this 21,72,51,54,55,102,101,105,121,34,33,88,38,85,86,47.My requirements is to extract data before the comma and insert the value ln another table. I am solved this problem just writing a store proc and writing a loop to extract the data. But my main requirements is Is it possible to write a function that working in cursor. If possible please help me.

regards:
Someswar
Re: Comma seperated value [message #322064 is a reply to message #322062] Thu, 22 May 2008 05:34 Go to previous messageGo to next message
govardhana_rg
Messages: 5
Registered: December 2007
Location: BANGALORE
Junior Member
Someshwar,

u can use collections rather than function..Perfomance wise it would be better.


....
Govardhana Gowda
Re: Comma seperated value [message #322065 is a reply to message #322062] Thu, 22 May 2008 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But my main requirements is Is it possible to write a function that working in cursor.

What does this mean? You want it in a single SQL statement?
This has been asked many times, for instance
http://www.orafaq.com/forum/m/315968/102589/?#msg_315968

Regards
Michel
Re: Comma seperated value [message #322307 is a reply to message #322065] Fri, 23 May 2008 01:29 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
Thank u for helping me. But this is not working. I am creating a table
CREATE TABLE SUD_CHECK
(
SUD VARCHAR2(100 BYTE)
)

and Insert The value like this
insert into sud_check values('10,1,1,1,1,1,1,1,1,4,0,1,90,1,0')

insert into sud_check values('10,1,1,1,1,1,1,1,1,1,1,4,0,1,90,94,1,0')
insert into sud_check values('10,1,1,1,1,1,1,8,0,1,1,1,1,1,1,1,1,0,0,0,0,0')
insert into sud_check values('10,1,1,1,1,1,1,1,1,4,0,1,90,1,0')
insert into sud_check values('10,1,1,1,1,1,1,1,1,4,0,1,90,1,0')

and then write the query which u given is

select
sud
,instr(sud,',',1,(lvl))
,instr(sud,',',1,(lvl+1))
,substr(sud,instr(sud,',',1,lvl)+1,instr(sud,',',1,lvl+1)-instr(sud,',',1,lvl)-1) sud,lvl
from (
select ','||sud||',' sud,lvl
from sud_check
,(select level lvl from dual connect by level < 100)
where lvl <= (length(sud)-length(replace(sud,',','')))+1)

but here only level 1 value 10 and sometimes 1 will be printed.
please assist me.

Regards:
Someswar
Re: Comma seperated value [message #322313 is a reply to message #322307] Fri, 23 May 2008 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section, use code tags.
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.

What did you try to fix it?

Regards
Michel

[Updated on: Fri, 23 May 2008 01:39]

Report message to a moderator

Re: Comma seperated value [message #322315 is a reply to message #322313] Fri, 23 May 2008 01:41 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
Sorry for that. If I insert single digit so how the value will be fetched corretly.
Regards:
someswar
Re: Comma seperated value [message #322327 is a reply to message #322315] Fri, 23 May 2008 02:02 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post example.
Also post what is your opinion, how have you to modify the query to make it work in this case?

Regards
Michel
Previous Topic: Need Suggestion Reg Tunning
Next Topic: Package Case Statement
Goto Forum:
  


Current Time: Mon Dec 05 10:48:10 CST 2016

Total time taken to generate the page: 0.20280 seconds