Home » SQL & PL/SQL » SQL & PL/SQL » Split string to rows
Split string to rows [message #229769] |
Tue, 10 April 2007 00:15  |
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 #229796 is a reply to message #229781] |
Tue, 10 April 2007 01:37   |
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   |
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   |
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   |
 |
Maaher
Messages: 7065 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
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 10:20:25 CST 2025
|