How I can split a varchar2 in stored function

[oceanju]

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

[Shestakov]

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;

[dknight]

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.

[oceanju]

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

[Shestakov]

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]

[oceanju]

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

[Shestakov]

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;

[oceanju]

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.