Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Unanswered: Parsing A Delimited String

    Hello Colleagues,

    I would like to take a delimited string and insert the data in a record. It is like as follows:

    1. Receive a delimited string ie. |dfafsf | dfafas | dfasdf |
    2. I have a table with fields say 1, 2,3.
    3. I would like to insert into the table the data in the delimited string according to the sequence of the field.
    4. The string will be an input to a procedure or function.

    Can you please help me on this.

    Thank you very much.

  2. #2
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    Java script

    This can be done with Perl or JavaScript. I do not know if it can be done in Pl/SQL.

    Here is an example of a simple Java program. This must be compiled using javac. There is an input file (X) and a output file(Y). After the java is compiled, then ran, run the Y.sql in Oracle. The file name is JavaParse.
    To compile: javac JavaParse.java
    To run: java JavaParse
    __________________________________________________ __
    import java.util.*;
    import java.io.*;

    class JavaParse
    {
    static public void main(String[] args)
    {
    try
    {
    BufferedReader in = new BufferedReader(new FileReader("C:\\X.txt"));//open the input file
    PrintWriter out = new PrintWriter(new BufferedWriter(new FileWriter("C:\\Y.sql")));//open the output file
    String strLine = in.readLine();//read the first line of text
    String strNull = "null";
    while(strLine != null)//while we have text to process
    {
    StringTokenizer tok = new StringTokenizer(strLine);//use the nifty tokenizer
    String strOne = tok.nextToken("|");
    String strTwo = tok.nextToken("|");
    String strThree = tok.nextToken("|");
    String strTest = "insert into table values " +
    "(" +
    "'" + strOne + "'," +
    "'" + strTwo + "'," +
    "'" + strThree + "'" +
    ");";
    out.println(strTest);//write the Y file
    strLine = in.readLine();//get the next line
    }//end while
    out.close();
    }//end try
    catch (Exception e)
    {
    e.printStackTrace();
    }//end catch
    }//end main
    }//end class JavaParse
    Michellea Southern-David

  3. #3
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    ok that was

    ok that was formatted, but then when it posted it is not formatted anymore. Sorry for the non formatted code.

    Copy and paste into a text file and it will still work...it is just not pretty.
    Michellea Southern-David

  4. #4
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Re: ok that was

    Originally posted by msouthern
    ok that was formatted, but then when it posted it is not formatted anymore. Sorry for the non formatted code.

    Copy and paste into a text file and it will still work...it is just not pretty.
    Hello Michellea,

    Thank you for your advise.

    Oracle 8i onward allows us to call an external routine. Can you help me structure the java pgm to that I can call it from a trigger accepting parameters from the calling trigger?

  5. #5
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: ok that was

    If you wanted to do it in Oracle. You can use

    SUBSTR(String,Start_Pos,Stop_Pos)
    will return a section of the string starting at Start_Pos, and ending at Stop_Pos.

    INSTR(String,Search_String,Start_Pos,Occurence)
    will return the position number of the Search_String starting from Start_Pos and for the Nth Occurence


    Insert into table_name
    Values (substr(lv_string,1,(instr(lv_string,'|',1,1)-1)),
    substr(lv_string,instr(lv_string,'|',1,1),(instr(l v_string,'|',1,2)-1)),
    substr(lv_string,instr(lv_string,'|',1,3),(instr(l v_string,'|',1,3)-1)));

    you will notice that to get the Stop_Pos you can use the INSTR function to determine where in the string the delimiter('|') is. You subtract 1 from that position so that the delimiter is not part of the field.

  6. #6
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Re: PARSING A DELIMITED STRING

    Originally posted by carloa
    If you wanted to do it in Oracle. You can use

    SUBSTR(String,Start_Pos,Stop_Pos)
    will return a section of the string starting at Start_Pos, and ending at Stop_Pos.

    INSTR(String,Search_String,Start_Pos,Occurence)
    will return the position number of the Search_String starting from Start_Pos and for the Nth Occurence


    Insert into table_name
    Values (substr(lv_string,1,(instr(lv_string,'|',1,1)-1)),
    substr(lv_string,instr(lv_string,'|',1,1),(instr(l v_string,'|',1,2)-1)),
    substr(lv_string,instr(lv_string,'|',1,3),(instr(l v_string,'|',1,3)-1)));

    you will notice that to get the Stop_Pos you can use the INSTR function to determine where in the string the delimiter('|') is. You subtract 1 from that position so that the delimiter is not part of the field.
    Hi... Thank you for the tip. Can you give more idea because I want to do the below:

    Insert into table_name
    Values (substr(lv_string,1,(instr(lv_string,'|',1,1)-1)),
    substr(lv_string,instr(lv_string,'|',1,1),(instr(l v_string,'|',1,2)-1)),
    substr(lv_string,instr(lv_string,'|',1,3),(instr(l v_string,'|',1,3)-1)));

    by looping. I am thinking of looping through the delimiter occurence. Like the below:

    counter := counter + 1;
    boolean := true;
    while boolean
    loop

    pos := instr(string,1,counter)

    i

  7. #7
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Re: PARSING A DELIMITED STRING

    Originally posted by carloa
    If you wanted to do it in Oracle. You can use

    SUBSTR(String,Start_Pos,Stop_Pos)
    will return a section of the string starting at Start_Pos, and ending at Stop_Pos.

    INSTR(String,Search_String,Start_Pos,Occurence)
    will return the position number of the Search_String starting from Start_Pos and for the Nth Occurence


    Insert into table_name
    Values (substr(lv_string,1,(instr(lv_string,'|',1,1)-1)),
    substr(lv_string,instr(lv_string,'|',1,1),(instr(l v_string,'|',1,2)-1)),
    substr(lv_string,instr(lv_string,'|',1,3),(instr(l v_string,'|',1,3)-1)));

    you will notice that to get the Stop_Pos you can use the INSTR function to determine where in the string the delimiter('|') is. You subtract 1 from that position so that the delimiter is not part of the field.
    Hi... Thank you for the tip. Can you give more idea because I want to do the below:

    Insert into table_name
    Values (substr(lv_string,1,(instr(lv_string,'|',1,1)-1)),
    substr(lv_string,instr(lv_string,'|',1,1),(instr(l v_string,'|',1,2)-1)),
    substr(lv_string,instr(lv_string,'|',1,3),(instr(l v_string,'|',1,3)-1)));

    by looping. I am thinking of looping through the delimiter occurence. Like the below:

    counter := counter + 1;
    boolean := true;
    while boolean
    loop
    counter := counter + 1;
    pos := instr(string,1,counter)
    if pos = 0 then
    boolean := false;
    end if;
    end loop

    However, I want to integrate the insert statement you indicated above in a dynamic manner. Is it possible?

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296

    Re: ok that was

    Originally posted by msouthern
    ok that was formatted, but then when it posted it is not formatted anymore. Sorry for the non formatted code.

    Copy and paste into a text file and it will still work...it is just not pretty.
    for formatting on posts use [ php ] and close with [ /php ]

    don't use spaces inside the crackets.

    so to format this:
    11:23:08 kod:latform> select * from customer where org_id = 4;

    CUST_ID ORG_ID LNAME_TXT
    --------------- ------------------------------ ----------------------------
    494258 4 Lanthorn
    494123 4 Lanthorn
    1235672 4 McGurk
    LT 4 Lanthorn 1
    192323 4 Gal
    1221COM 4 CEO
    123COM 4 CFO
    1COM 4 Phyl
    77 4 Joe John Joey Johm Shabadew
    9876512 4 Production
    ABCD 4 test

    I just enclose it like above:
    PHP Code:
    11:23:08 kod:latformselect from customer where org_id 4;

    CUST_ID         ORG_ID                         LNAME_TXT                   
    --------------- ------------------------------ ----------------------------
    494258          4                              Lanthorn                    
    494123          4                              Lanthorn                    
    1235672         4                              McGurk                      
    LT              4                              Lanthorn 1                  
    192323          4                              Gal                         
    1221COM         4                              CEO                         
    123COM          4                              CFO                         
    1COM            4                              Phyl                        
    77              4                              Joe John Joey Johm Shabadew 
    9876512         4                              Production                  
    ABCD            4                              test 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Re: ok that was

    Originally posted by The_Duck
    for formatting on posts use [ php ] and close with [ /php ]

    don't use spaces inside the crackets.

    so to format this:
    11:23:08 kod:latform> select * from customer where org_id = 4;

    CUST_ID ORG_ID LNAME_TXT
    --------------- ------------------------------ ----------------------------
    494258 4 Lanthorn
    494123 4 Lanthorn
    1235672 4 McGurk
    LT 4 Lanthorn 1
    192323 4 Gal
    1221COM 4 CEO
    123COM 4 CFO
    1COM 4 Phyl
    77 4 Joe John Joey Johm Shabadew
    9876512 4 Production
    ABCD 4 test

    I just enclose it like above:
    PHP Code:
    11:23:08 kod:latformselect from customer where org_id 4;

    CUST_ID         ORG_ID                         LNAME_TXT                   
    --------------- ------------------------------ ----------------------------
    494258          4                              Lanthorn                    
    494123          4                              Lanthorn                    
    1235672         4                              McGurk                      
    LT              4                              Lanthorn 1                  
    192323          4                              Gal                         
    1221COM         4                              CEO                         
    123COM          4                              CFO                         
    1COM            4                              Phyl                        
    77              4                              Joe John Joey Johm Shabadew 
    9876512         4                              Production                  
    ABCD            4                              test 
    Hi... The delimited string will come from a trigger calling a stored function or procedure. The below tip is what I would like to head for:

    Insert into table_name
    Values (substr(lv_string,1,(instr(lv_string,'|',1,1)-1)),
    substr(lv_string,instr(lv_string,'|',1,1),(instr(l
    v_string,'|',1,2)-1)),
    substr(lv_string,instr(lv_string,'|',1,3),(instr(l
    v_string,'|',1,3)-1)));

    I am thinking of looping through the delimiter occurence. Like the below:

    counter := counter + 1;
    boolean := true;
    while boolean
    loop
    counter := counter + 1;
    pos := instr(string,1,counter)
    if pos = 0 then
    boolean := false;
    end if;
    end loop

    Can you give me some advise on doing this dynamically.?

  10. #10
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: ok that was

    need some more info. What do you plan to do as you parse the string? Do you plan to insert each chunk into a new record, or each chunk parsed will be a different column in the same record?

  11. #11
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Re: ok that was

    Originally posted by carloa
    need some more info. What do you plan to do as you parse the string? Do you plan to insert each chunk into a new record, or each chunk parsed will be a different column in the same record?
    Hi... I plan to take each chunk parsed to a different column in the same record. Each chunk will have to be loaded in the correct correspoding field. The assumption here is the first chunk corresponds to the first field of the same record and so on. I will continue loading each field of the record until I reach the end of the string. Finally, I will insert it as a whole record.

    Please look at the below example:

    | a | b | c | d |

    record fields as defined during the create table stage:

    col1
    col2
    col3
    col4

    col1 := a col2 := b col3 := c col4 := d and so on.

  12. #12
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: ok that was

    I'm gonna try the formatting suggested by the duck. Hope it looks good
    PHP Code:
    Declare

    TYPE lv_array IS TABLE OF VARCHAR2(100)
       
    INDEX BY BINARY_INTEGER;
       
    lv_columns     lv_array;
    lv_string      varchar2(50) := 'A | B | C | D |';
    lv_delim       char(1) := '|';
    lv_counter     pls_integer := 0;
    lv_str         pls_integer := 1;
    lv_stp         pls_integer;
    lv_sql_line1   varchar2(500);
    lv_sql_line2   varchar2(500);

    Begin

    -- Set initial stop position 
    lv_stp 
    := Instr(lv_string,lv_delim,lv_str,1);

    While 
    lv_stp != 0
    Loop

       
    -- Increment counter
       lv_counter 
    := lv_counter 1;

       -- 
    Set array element to the trim of the parsed string 
       lv_columns
    (lv_counter) := Trim(Substr(lv_string,lv_str,(lv_stp-lv_str)));

       -- 
    Set starting position to where parseing stopped 
       lv_str 
    := lv_stp 1;
       
       -- 
    Set stop position to next occurence of delimiter 
       lv_stp 
    := Instr(lv_string,lv_delim,lv_str,1);
       

    End Loop;

    -- 
    Set up initial part of sql statement 
    lv_sql_line1 
    := 'Insert Into Table_Name (';
    lv_sql_line2 := 'Values (';

    For 
    lv_cnt in 1..lv_counter Loop

       
    -- Add columen name to sql statement 
       lv_sql_line1 
    := lv_sql_line1 || 'Column_Name'||lv_cnt||',';
       
       -- 
    Add corresponding column value to sql statment 
       lv_sql_line2 
    := lv_sql_line2 ||''''||lv_columns(lv_cnt)||''','

    End Loop;

    -- 
    Remove last comma, and add in the Paren 
    lv_sql_line1 
    := substr(lv_sql_line1,1,(length(lv_sql_line1)-1))||')';
    lv_sql_line2 := substr(lv_sql_line2,1,(length(lv_sql_line2)-1))||')';

    -- 
    Execute the Insert
    Execute Immediate lv_sql_line1
    ||' '||lv_sql_line2;
    dbms_output.put_line(lv_sql_line1);
    dbms_output.put_line(lv_sql_line2);

    End
    This will result in
    PHP Code:
    Insert Into Table_Name (Column_Name1,Column_Name2,Column_Name3,Column_Name4)
    Values ('A','B','C','D'

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    nice formatting!

    It is visually superior and easy to read
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27
    Originally posted by The_Duck
    nice formatting!

    It is visually superior and easy to read
    Hello The_Duck

    I am now trying the solution you advised me. I am trying to make a little modification to suit my requirement. However, I have one more problem. How can I integrate the input mask for each of the field. In one table I may have a combination of numeric fields and date fields in it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •