|
View previous topic :: View next topic
|
| Author |
Message |
swatheeswaran
New User
Joined: 04 Nov 2008
Posts: 7
Location: Chennai
|
|
|
|
How to split a column of VARCHAR(4000) into multiple rows of length 80 each using SQL query.
For example. a column has ABCDEFGHIJ,for length 2, o/p should be
AB
CD
EF
GH
IJ |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you write a tiny bit of program code to read the varchar, split the data, and either write a sequential file to LOAD or directly INSERT the new rows. If the volume is high, the inserts will probably not perform acceptably. . . |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
It's do-able, maybe not desirable :
| Code: |
with cnt(stpos) as (
select 1 from sysibm.sysdummy1
union all
select stpos + 80 from cnt where stpos < 3920)
select substr(vchar_column,stpos, min(80,length(vchar_column) - stpos + 1)) from Table1 , cnt
where stpos <= length(vchar-column)
and table1.pk = ? |
|
|
| Back to top |
|
|
|
|
