-
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 @poratips@hotmail.com that also good.
Regards,
Prashant
-
Re: Parse the value
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);
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|