I have data like this, ColA(int) and ColB(varchar(1024))
In ColB, data are in multiple sets like, first 2 characters are numeric and later 5 chars are alphabetic, now this multiple sets of data in a row needs to be inserted into a separate table in different rows and any data after 99(as 2 first chars in a set)can be ignore/discared in a row.
Currently i have following 2 Options,
I can prepare query like below using unions, since the row has around 88 sets of data, 88 unions might be required(not the best method, 88 tablespace scans, noway)
SELECT ID,SUBSTR(DUMMY_TEXT,1,2), SUBSTR(DUMMY_TEXT,3,5)
SELECT ID,SUBSTR(DUMMY_TEXT,8,2), SUBSTR(DUMMY_TEXT,10,5)
One-time conversion cobol program(only the best method, i can think of right now, because will involve only one tablespace scan).
I just want to know, if there is anyother way of doing it.
Wow that's really superb, GuyC
The Real table has 20 million rows, Size of this Varchar column is 21000 and it contains mixture of DATE, CHAR and cobol COMP-3 values stored directly into this column. So, currently the query involves joins, conversions from COMP-3 to numeric and other things, there will be huge number of functions involved.
I will check up with the test data with this approach.