Parse the value

[poratips]

I have flat file test.txt is like:
id1 prashant ABCDEF
id2 pds CDHI
id3 pdshah ABCD
id4 pshah ABCDEFHIJKLM
id5 bako XY
..
Now I am loading this file into EXT table. So Table exactly looks like above file.
CUST_EXT table:
id Name DivCode
===== ======= =========
id1 prashant ABCDEF
id2 pds CDHI
id3 pdshah ABCD
id4 pshah ABCDEFHIJKLM
id5 bako XY
..

How can I make records like:????
id Name DivCode
===== ======= =========
id1 prashant AB
id1 prashant CD
id1 prashant EF

id2 pds CD
id2 pds HI

id3 pdshah AB
id3 pdshah CD

id4 pshah AB
id4 pshah CD
id4 pshah EF
id4 pshah HI
id4 pshah JK
id4 pshah LM

id5 bako XY

FYI:
I did if the records are AB CD EF but I am unable to parse the record if ABCDEF?
Your help will be really appreciated.
If you send me @poratips@hotmail.com that also good.


Regards,
Prashant

[DaPi]

Originally posted by poratips
I did if the records are AB CD EF but I am unable to parse the record if ABCDEF?

Huh?

If you're just splitting it up two chars at a time, use SUBSTR:
SELECT . . . INTO my_char . . . ;
then loop over:
INSERT . . . SUBSTR(my_char,1,2) . . . ;
my_char := SUBSTR(my_char,3);

[tamilselvan]

You can do it PL/SQL procedure.
Or
You can do it in the following method:

SQL> select * from t1 ;

        ID SOMEX
---------- ------------------------------
         1 ABCD
         2 ABCDEF

SQL> select *
  2  from ( select id, substr(somex,1,2)
  3          from  t1 where substr(somex,1,2) is not null
  4         union all
  5         select id, substr(somex,3,2)
  6          from t1 where substr(somex,3,2) is not null
  7         union all
  8         select id, substr(somex,5,2)
  9          from  t1 where substr(somex,5,2) is not null)
 10  ;

        ID SU
---------- --
         1 AB
         1 CD
         2 AB
         2 CD
         2 EF

SQL> spool off 

Note: If the SOMEX col is too big, then you have to write many union
all statement.

Tamil