Parse the value

Printable View

12-03-2004, 06:54 PM
poratips

Parse the value

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 @[email protected] that also good.

Regards,
Prashant
12-04-2004, 06:39 AM
DaPi

Re: Parse the value

Quote:

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);
12-04-2004, 05:35 PM
tamilselvan

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

PHP Code:

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