Home » SQL & PL/SQL » SQL & PL/SQL » CONNECT BY Issue (Oracle 10g)
CONNECT BY Issue [message #402932] Wed, 13 May 2009 04:09 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
How to convert the comma seperated columns into rows
without using CONNECT BY.

Ex:

Column1
-------------------------
1001,Raja,600,Account

I need like this

Column1
--------------------------
1001
Raja
600
Account

How to achieve this?? Since i used connect by in the query
in PL/SQL Code the process is dead slow..

Expecting any one of the reply.
Re: CONNECT BY Issue [message #402937 is a reply to message #402932] Wed, 13 May 2009 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to choose, connect by or PL/SQL.
If you know the maximum number of commas, you can use "union all" to generate the rows from dual.

Regards
Michel

[Updated on: Wed, 13 May 2009 04:24]

Report message to a moderator

Re: CONNECT BY Issue [message #402947 is a reply to message #402932] Wed, 13 May 2009 04:41 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
I am using the connect by used query in inside the PL/SQL Program.

I know the maximum commas. But how to achive by using
Union ALL. Using Substr, Instr??

Can you please gimme some example..?

Re: CONNECT BY Issue [message #402948 is a reply to message #402947] Wed, 13 May 2009 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many and many and many examples here (and elsewhere) to break down a list string into rows.
It is useless I add one more.

Regards
Michel

[Updated on: Wed, 13 May 2009 04:50]

Report message to a moderator

Re: CONNECT BY Issue [message #402959 is a reply to message #402932] Wed, 13 May 2009 05:04 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
I did not find exact. Can u please post any URL.. would be great.
Re: CONNECT BY Issue [message #402973 is a reply to message #402932] Wed, 13 May 2009 05:42 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
An Example
SELECT t1.*,
       TRIM (SUBSTR (',' || t1.tcd,INSTR (',' || t1.tcd, ',', 1, rn) + 1,
INSTR (',' || t1.tcd, ',', 1, rn + 1)- 1- INSTR (',' || t1.tcd, ',', 1, rn))) tcd_single
  FROM (SELECT md.*, LENGTH (tcd) - LENGTH (REPLACE (tcd, ',', '')) lll
          FROM (SELECT '123,456,789,' tcd
                  FROM DUAL) md) t1,
       (SELECT ROWNUM rn
          FROM all_objects
         WHERE ROWNUM < 1000) t2
 WHERE t2.rn <= t1.lll

[Updated on: Wed, 13 May 2009 06:06] by Moderator

Report message to a moderator

Re: CONNECT BY Issue [message #402997 is a reply to message #402932] Wed, 13 May 2009 06:42 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Cool man.
Re: CONNECT BY Issue [message #403009 is a reply to message #402932] Wed, 13 May 2009 07:55 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I dont know how you are using Connect By but for me connect by works well
  SELECT distinct REGEXP_SUBSTR('123,234,456', '[^' || ',' || ']+', 1, LEVEL) a
   FROM dual
   CONNECT BY REGEXP_SUBSTR('123,234,456', '[^' || ','  || ']+', 1, LEVEL) IS NOT NULL
   ORDER BY a
Re: CONNECT BY Issue [message #403014 is a reply to message #403009] Wed, 13 May 2009 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works well for anyone... but those that don't post their code. Wink

Regards
Michel
Re: CONNECT BY Issue [message #403395 is a reply to message #403009] Fri, 15 May 2009 05:56 Go to previous messageGo to next message
Dagur
Messages: 2
Registered: May 2009
Location: Iceland
Junior Member
Or even simpler

  SELECT distinct REGEXP_SUBSTR('123,234,456', '\d+', 1, LEVEL) a, LEVEL
   FROM dual
   CONNECT BY REGEXP_SUBSTR('123,234,456', '\d+', 1, LEVEL) IS NOT NULL
   ORDER BY a
Re: CONNECT BY Issue [message #403398 is a reply to message #403395] Fri, 15 May 2009 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But the question was:
Quote:
without using CONNECT BY.


Regards
Michel
Re: CONNECT BY Issue [message #403399 is a reply to message #403398] Fri, 15 May 2009 06:08 Go to previous messageGo to next message
Dagur
Messages: 2
Registered: May 2009
Location: Iceland
Junior Member
Yeah, sorry. This was supposed to be a reply to ayush_anand's post. I thought his regex was a bit weird.
Re: CONNECT BY Issue [message #403407 is a reply to message #403399] Fri, 15 May 2009 06:52 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right, your one is, in my opinion, better for numbers but OP posts an example with not numeric values.

Regards
Michel
Previous Topic: Oracle_XMLTYPE
Next Topic: Need help on changing all number in once
Goto Forum:
  


Current Time: Sat Dec 03 03:33:32 CST 2016

Total time taken to generate the page: 0.13275 seconds