jwright9
asked on
Splitting a long text string in PL/SQL into individual words and storing the individual words in a table.
I found the split function below at this URL:
http://builder.com.com/5100-6388_14-5259821.html
create or replace function split(p_list in varchar2, p_del in varchar2 := ',') return split_tbl pipelined is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
--l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx- 1));
l_list := substr(l_list,l_idx+length (p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
It works in that it stores each of the words split out from the longer string are store in a temporary table of sorts. I don't think this table has a name.
Is there a way that I can store theses words in a normal Oracle table so that I can use them again later in the program I am calling the split function in? I want to put the words in a table and sort them by their length (the number of characters in the words).
I'm assigning 500 points to this question since I think its hard and I needed this process to work a few hours ago.
Thanks.
http://builder.com.com/5100-6388_14-5259821.html
create or replace function split(p_list in varchar2, p_del in varchar2 := ',') return split_tbl pipelined is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
--l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-
l_list := substr(l_list,l_idx+length
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
It works in that it stores each of the words split out from the longer string are store in a temporary table of sorts. I don't think this table has a name.
Is there a way that I can store theses words in a normal Oracle table so that I can use them again later in the program I am calling the split function in? I want to put the words in a table and sort them by their length (the number of characters in the words).
I'm assigning 500 points to this question since I think its hard and I needed this process to work a few hours ago.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks geotiger for the speed and the nice working solution you gave me. RC and P thanks for the variations and additional technical detail. I appreciate your help.
Sorry, My solution is for Oracle 8i.
J.
I am glad that it works for you. It was my pleasure to help.
GT
I am glad that it works for you. It was my pleasure to help.
GT
The view is a good approach, but geotiger has the correct way to do it if you really do want to have a true table with the data repeated in two places.