Home » SQL & PL/SQL » SQL & PL/SQL » Split string to rows
Split string to rows [message #229769] Tue, 10 April 2007 00:15 Go to next message
saikatermail
Messages: 20
Registered: November 2006
Location: Hyderabad
Junior Member
Hi,

My table t1 has column c1. Content of c1 is as following

o,r,a,c,l,e
i,n,f,o,r,m,i,x

How to get the output as following using "select"


1 o
1 r
1 a
1 c
1 l
1 e
2 i
2 n
2 f
2 o
2 r
2 m
2 i
2 x

Regards

Saikat

[Updated on: Tue, 10 April 2007 02:37] by Moderator

Report message to a moderator

Re: SQL Help [message #229771 is a reply to message #229769] Tue, 10 April 2007 00:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
The homework tutor is down the hall, 2nd door on the right.
Show us what you've done & the reults.
Explain what is & is not working & how to make it better.
Re: SQL Help [message #229781 is a reply to message #229769] Tue, 10 April 2007 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Erased as I misread the question (actually I read it as the opposite).

[Updated on: Tue, 10 April 2007 01:47]

Report message to a moderator

Re: SQL Help [message #229796 is a reply to message #229781] Tue, 10 April 2007 01:37 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi,

doing this way

create table srinu23 as
(select 'o,r,a,c,l,e' as name from dual)

select '1',substr(num,level,1) from
(select replace(name,',','') as num from srinu23)
connect by level<=length(num)

thanks
srinivas
Re: SQL Help [message #229799 is a reply to message #229796] Tue, 10 April 2007 01:40 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
THis is perfect query.


select num1,substr(num,level,1) from 
(select rownum as num1, replace(name,',','') as num from srinu23)
connect by level<=length(num)


thanks,
srinivas
Re: SQL Help [message #229807 is a reply to message #229796] Tue, 10 April 2007 01:48 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

see this

create table srinu24 as
(select 'o,r,a,c,l,e' as name from dual
union
select 's,r,i,n,i,v,a,s' as name from dual
)

select Distinct num1,substr(num,level,1) as num2 from
(select rownum as num1, replace(name,',','') as num from srinu24)
connect by level<=length(num)

thanks
srinivas
Re: Split string to rows [message #229838 is a reply to message #229807] Tue, 10 April 2007 02:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Stolen from here:
WITH yourtable AS
     (SELECT 'o,r,a,c,l,e' thecol
      FROM   DUAL
      UNION
      SELECT 'i,n,f,o,r,m,i,x' thecol
      FROM   DUAL)
SELECT rownum#
     , code
FROM   (SELECT DISTINCT rownum#
                      , LEVEL
                      , SUBSTR (str
                              , INSTR (str, base, 1, LEVEL) + len
                              ,   INSTR (str, base, 1, LEVEL + 1)
                                - INSTR (str, base, 1, LEVEL)
                                - len
                               ) code
        FROM            (SELECT ROWNUM rownum#
                              , ',' base
                              , ',' || thecol || ',' str
                              , LENGTH (',') len
                         FROM   yourtable)
        CONNECT BY      LEVEL <=
                               (LENGTH (str) - LENGTH (REPLACE (str, base)))
                             / len
                           - 1)


Notice that I've changed your subject. Thanks to vague topic titles like yours we have a VERY hard time finding these threads back and we can start all over again. Next time choose the title more carefully. Thanks.

MHE
Re: Split string to rows [message #229946 is a reply to message #229769] Tue, 10 April 2007 06:39 Go to previous message
saikatermail
Messages: 20
Registered: November 2006
Location: Hyderabad
Junior Member
Thanks & Regards

Saikat
Previous Topic: DDL statement on table on which materialized view is created
Next Topic: Create View Problem
Goto Forum:
  


Current Time: Thu Dec 08 16:12:48 CST 2016

Total time taken to generate the page: 0.08613 seconds