I have an oracle table that has a varchar2(4000) byte column with no delimiters. I need to parse the text and display a row after every 40 characters.
Example - varchar2(4000)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbccccccccccccccccccccccccccccccddddddddddddddddddd......
I need output like
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
cccccccccccccccccccccccccccccccccccccccc
Need a SQL query using instr or substr. The table dual also if possible
ops$tkyte%ORA11GR2> create table t ( id number, x varchar2(4000) );
Table created.
ops$tkyte%ORA11GR2> declare
2 l_data long;
3 begin
4 for i in 1 .. 1000
5 loop
6 l_data := l_data || 'hello world ';
7 exit when (length(l_data) > 2000);
8 end loop;
9 insert into t (id,x) values ( 1, l_data );
10 l_data := null;
11
12 for i in 1 .. 1000
13 loop
14 l_data := l_data || 'and the answer is ';
15 exit when (length(l_data) > 3000);
16 end loop;
17 insert into t (id,x) values ( 1, l_data );
18 end;
19 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select id, column_value, substr( x, (40*column_value)+1, 40 )
2 from t, table( cast( multiset( select level-1 r
3 from dual
4 connect by level <= length(t.x)/40+1 )
5 as sys.odciNumberList ) )
6 /
ID COLUMN_VALUE SUBSTR(X,(40*COLUMN_VALUE)+1,40)
---------- ------------ ----------------------------------------
1 0 hello world hello world hello world hell
1 1 o world hello world hello world hello wo
1 2 rld hello world hello world hello world
1 3 hello world hello world hello world hell
1 4 o world hello world hello world hello wo
1 5 rld hello world hello world hello world
1 6 hello world hello world hello world hell
1 7 o world hello world hello world hello wo
1 8 rld hello world hello world hello world
1 9 hello world hello world hello world hell
1 10 o world hello world hello world hello wo
1 11 rld hello world hello world hello world
1 12 hello world hello world hello world hell
1 13 o world hello world hello world hello wo
1 14 rld hello world hello world hello world
1 15 hello world hello world hello world hell
1 16 o world hello world hello world hello wo
1 17 rld hello world hello world hello world
1 18 hello world hello world hello world hell
1 19 o world hello world hello world hello wo
1 20 rld hello world hello world hello world
1 21 hello world hello world hello world hell
1 22 o world hello world hello world hello wo
1 23 rld hello world hello world hello world
1 24 hello world hello world hello world hell
1 25 o world hello world hello world hello wo
1 26 rld hello world hello world hello world
1 27 hello world hello world hello world hell
1 28 o world hello world hello world hello wo
1 29 rld hello world hello world hello world
1 30 hello world hello world hello world hell
1 31 o world hello world hello world hello wo
1 32 rld hello world hello world hello world
1 33 hello world hello world hello world hell
1 34 o world hello world hello world hello wo
1 35 rld hello world hello world hello world
1 36 hello world hello world hello world hell
1 37 o world hello world hello world hello wo
1 38 rld hello world hello world hello world
1 39 hello world hello world hello world hell
1 40 o world hello world hello world hello wo
1 41 rld hello world hello world hello world
1 42 hello world hello world hello world hell
1 43 o world hello world hello world hello wo
1 44 rld hello world hello world hello world
1 45 hello world hello world hello world hell
1 46 o world hello world hello world hello wo
1 47 rld hello world hello world hello world
1 48 hello world hello world hello world hell
1 49 o world hello world hello world hello wo
1 50 rld
1 0 and the answer is and the answer is and
1 1 the answer is and the answer is and the
1 2 answer is and the answer is and the answ
1 3 er is and the answer is and the answer i
1 4 s and the answer is and the answer is an
1 5 d the answer is and the answer is and th
1 6 e answer is and the answer is and the an
1 7 swer is and the answer is and the answer
1 8 is and the answer is and the answer is
1 9 and the answer is and the answer is and
1 10 the answer is and the answer is and the
1 11 answer is and the answer is and the answ
1 12 er is and the answer is and the answer i
1 13 s and the answer is and the answer is an
1 14 d the answer is and the answer is and th
1 15 e answer is and the answer is and the an
1 16 swer is and the answer is and the answer
1 17 is and the answer is and the answer is
1 18 and the answer is and the answer is and
1 19 the answer is and the answer is and the
1 20 answer is and the answer is and the answ
1 21 er is and the answer is and the answer i
1 22 s and the answer is and the answer is an
1 23 d the answer is and the answer is and th
1 24 e answer is and the answer is and the an
1 25 swer is and the answer is and the answer
1 26 is and the answer is and the answer is
1 27 and the answer is and the answer is and
1 28 the answer is and the answer is and the
1 29 answer is and the answer is and the answ
1 30 er is and the answer is and the answer i
1 31 s and the answer is and the answer is an
1 32 d the answer is and the answer is and th
1 33 e answer is and the answer is and the an
1 34 swer is and the answer is and the answer
1 35 is and the answer is and the answer is
1 36 and the answer is and the answer is and
1 37 the answer is and the answer is and the
1 38 answer is and the answer is and the answ
1 39 er is and the answer is and the answer i
1 40 s and the answer is and the answer is an
1 41 d the answer is and the answer is and th
1 42 e answer is and the answer is and the an
1 43 swer is and the answer is and the answer
1 44 is and the answer is and the answer is
1 45 and the answer is and the answer is and
1 46 the answer is and the answer is and the
1 47 answer is and the answer is and the answ
1 48 er is and the answer is and the answer i
1 49 s and the answer is and the answer is an
1 50 d the answer is and the answer is and th
1 51 e answer is and the answer is and the an
1 52 swer is and the answer is and the answer
1 53 is and the answer is and the answer is
1 54 and the answer is and the answer is and
1 55 the answer is and the answer is and the
1 56 answer is and the answer is and the answ
1 57 er is and the answer is and the answer i
1 58 s and the answer is and the answer is an
1 59 d the answer is and the answer is and th
1 60 e answer is and the answer is and the an
1 61 swer is and the answer is and the answer
1 62 is and the answer is and the answer is
1 63 and the answer is and the answer is and
1 64 the answer is and the answer is and the
1 65 answer is and the answer is and the answ
1 66 er is and the answer is and the answer i
1 67 s and the answer is and the answer is an
1 68 d the answer is and the answer is and th
1 69 e answer is and the answer is and the an
1 70 swer is and the answer is and the answer
1 71 is and the answer is and the answer is
1 72 and the answer is and the answer is and
1 73 the answer is and the answer is and the
1 74 answer is and the answer is and the answ
1 75 er is
127 rows selected.