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
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.