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?
123456789123456789123456789123456789123456789123456789123456789123456789
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?
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.
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.
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)
