Re: Parsing A String and Inserting

E. Scott wrote:
> I have a small table that looks like the following:
>
> TEST_TABLE_1
>
> TXN_ID NUM(9)
> STRINGFIELD VARCHAR2(90)
>
> If TXN_ID is 123 and STRINGFIELD is 333.444.5555.666.
>
> I need a procedure or function that will parse this string and insert
> rows in another table like this:
>
> TEST_TABLE_2
>
> TXN_ID SEQ_NUM NEW_STRING
> 123 1 333
> 123 2 444
> 123 3 5555
> 123 4 666
>
> I know the INSTR Function can be used in some kind of loop but I am
> not sure what combination of functions to use to know when end of
> string is hit or best method for optimization. Could be large volume
> of rows.
>
> Any help appreciated.
>
> Scott

A bit of PLSQL programming and INSTR and SUBSTR can do the job.

• Open an outside cursor loop on test_table_1
• Declare a e.g. startpos and endpos variables. Set startpos to 1 and endpos to 0. Now start a loop and get endpos index of the first delimiter i.e. '.' by using INSTR relative to startpos (..., '.', startpos, 1). Use SUBSTR and extract (..., startpos, endpos - startpos). Do the insert etc and before going to the next iteration in the inside loop, set startpos := endpos + 1. Exit the loop if endpos = 0. OR
• If you are on 8i and above and have JServer, other option is to simply create a String[] array by using String.split() function and loop thru the elements.

You can tidy it up and test it.

Regards
/Rauf Received on Mon Mar 14 2005 - 18:15:04 CST