Link to home
Start Free TrialLog in
Avatar of jwright9
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.
ASKER CERTIFIED SOLUTION
Avatar of geotiger
geotiger

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RCorfman
RCorfman

I would also note that the advantage to the approach shown in my example is there is no need to create a seperate split_tbl type. This is all packaged up as part of the package.

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.
Avatar of jwright9

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