CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
spider8
spider8

Reputation: 157

Split one row into multiple rows (fixed width)

One row of incoming record has multiple sub-records concatenated. Each sub record is 9 characters long. I have 8 such sub-records in each row. So each row is (8x9=72 char). Is there a way i can split 1 record into 8 records here?

Input

123456789123456789123456789123456789123456789123456789123456789123456789

Output

123456789 
123456789
123456789
123456789
123456789
123456789
123456789
123456789

I know i can do this with 8 sub queries and union them .. is there a better way?

Upvotes: 1

Views: 6367

Answers (3)

Kevin Burton
Kevin Burton

Reputation: 11936

or how about this:

SELECT substr(t1.astr,n*9+1,9) 
FROM t1,
(SELECT ROWNUM-1 n FROM dual CONNECT BY LEVEL <= 8) t2

table t1 contains your strings, table t2 is a generated list of sequential numbers from 0 to 7 by cross joining it to the t1 table we can use it to cut up the string column.

Upvotes: 2

John Weber
John Weber

Reputation: 193

If it is predictable that each line will always split into a fixed number of lines, something like the following should be possible:

select  
    b.iterator,
    substr(a.mystring,b.iterator*9-8,9) as split
from

(select '123456789123456789123456789123456789123456789123456789123456789123456789' as mystring from dual) a,
(select 1 as iterator from dual union
 select 2 as iterator from dual union
 select 3 as iterator from dual union
 select 4 as iterator from dual union
 select 5 as iterator from dual union
 select 6 as iterator from dual union
 select 7 as iterator from dual union
 select 8 as iterator from dual) b

EDIT: Kevin's iterator was simpler and better than my brute force version. The b subquery shoud be (SELECT ROWNUM as iterator FROM dual CONNECT BY LEVEL <= 8)

Upvotes: 1

Johan B.W. de Vries
Johan B.W. de Vries

Reputation: 391

I've seen people use a secondary table containing only the values 1 through 8, then joining with that table. You can use the value to substring your record.

Something like 

SELECT SUBSTRING(long_val, eight.value * 9, 9)
FROM mytable, eight

Note: Query untested, but I hope the idea gets across.

Upvotes: 1

Related Questions