Parse the value
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Parse the value

  1. #1
    Join Date
    Oct 2004
    Posts
    9

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    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);

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    You can do it PL/SQL procedure.
    Or
    You can do it in the following method:
    PHP Code:
    SQLselect from t1 ;

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

    SQL
    select *
      
    2  from select idsubstr(somex,1,2)
      
    3          from  t1 where substr(somex,1,2is not null
      4         union all
      5         select id
    substr(somex,3,2)
      
    6          from t1 where substr(somex,3,2is not null
      7         union all
      8         select id
    substr(somex,5,2)
      
    9          from  t1 where substr(somex,5,2is 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
  •  



Click Here to Expand Forum to Full Width