How I can split a varchar2 in stored function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How I can split a varchar2 in stored function

  1. #1
    Join Date
    Sep 2001
    Posts
    99
    I used a stored function which has a varchar2 parameter. The parameter is a id list, the format is like follows:
    '12|23|45|67|'
    It's means there is a 4 id in this string,there is no delimiter before the first item and there is the delimiter after the last item. If there is only one item in this list, the famat is like follows:
    '12|'
    If there is no item in the list, the list is ''
    I want split the string, if the string is '12|23|45|67', I want to get an array variable which have 4 elements. The elements' type is varchar2, the value is '12','23','45' and '67' respectively.
    please help
    thanks
    ocean

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    U have 2 ways to solve ur problem:

    1. use
    DBMS_UTILITY.COMMA_TO_TABLE (
    list IN VARCHAR2,
    tablen OUT BINARY_INTEGER,
    tab OUT UNCL_ARRAY);

    2. white ur own small program:

    type tb_numbers is table of varchar2(100) index by binary integer;
    nmt tb_numbers ;l
    i := 0;
    j := 1;
    while insrt(str_temp.'|',i,1) > 0 loop
    i := i +1;
    nmt(i) := substr(tmp_str,j, insrt(str_temp.'|',i,1)-1);
    j := insrt(str_temp.'|',i,1) + 1;
    end loop;

  3. #3
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Since all of your data is not available to me, it is enough to say that you should use the functions SUBSTR and INSTR. If you have a copy of the Oracle Complete Reference, it will be very clear.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  4. #4
    Join Date
    Sep 2001
    Posts
    99

    thanks, a another question

    shestakov: thanks ,but I want to know how is the binary_integer? can you tell me the DBMS_UTILITY.COMMA_TO_TABLE details? I want to know the parameters' means.

    dknight: I have no oracle complete reference ,can you tell me a url? in oracle , the document is online, I want to a zip package so I can download it on my local computer. By the way, I have no oracle CD
    ocean

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    1)
    Binary_integer is a special numeric data type in PL/SQL.
    U can use this type for indexing "in memory" tables (type tabe inPL/SQL)

    2)
    in comma_to_table:

    list IN VARCHAR2, -- source string
    tablen OUT BINARY_INTEGER, -- (return value) number of elements in table
    tab OUT UNCL_ARRAY); -- result table(list) of varchar2 values.
    -------------------------------------------
    PS:
    comma_to_table use ',' as delimiter, u should replace ur'|' to ','

    source_str varchar2(100) := '12|23|56|78';
    ret_num number;
    ret_tbl dbms_utility.uncl_array;

    dbms_utility.comma_to_table(replace(source_str,'|',','), ret_num, ret_tbl);

    [Edited by Shestakov on 03-05-2002 at 05:17 PM]

  6. #6
    Join Date
    Sep 2001
    Posts
    99

    thanks but still have anothor question

    in comma_to_table, must the delimiter be the ","? If the there is a "," in the string? How I can do?
    for example:
    the sourse string: '12,34|23,34|34,23|'
    if I replace "|" to ",", the string will convert to '12,34,23,34,34,23'.
    The reason why I use the '|' as the delimiter is the '|' can not in the item.
    please help.
    thanks
    ocean

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Just do:

    dbms_utility.comma_to_table(replace(replace(source_str,',','@'),'|',','), ret_num, ret_tbl);
    for i in ret_tbl.first..ret_tbl.last loop
    ret_tbl(i) := replace(ret_tbl(i),'@',');
    end loop;

  8. #8
    Join Date
    Sep 2001
    Posts
    99

    thank you but the excute efficiency?

    of course I know this method, but I'm afraid the split time is too long. Because in the same time, the server will receive many requests. but I think I must do this used this method.
    thanks.
    ocean

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