Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to split a column of VARCHAR(4000) into multiple rows.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
swatheeswaran

New User


Joined: 04 Nov 2008
Posts: 7
Location: Chennai

PostPosted: Sun Feb 21, 2010 2:58 pm    Post subject: How to split a column of VARCHAR(4000) into multiple rows.
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Feb 22, 2010 12:03 am    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Mon Feb 22, 2010 2:48 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us