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.
jwright9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

geotigerCommented:
Yes, here is how you can do that:

SQL>create table xx as select * from table(split('one,two,three'));

Table created.

SQL> desc xx
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 COLUMN_VALUE                                       VARCHAR2(4000)

SQL> select * from xx;

COLUMN_VALUE
--------------------------------------------------------------------------------
one
two
three

SQL> insert into xx select * from table(split('1,2,3'));

3 rows created.

SQL> select * from xx;

COLUMN_VALUE
--------------------------------------------------------------------------------
one
two
three
1
2
3

6 rows selected.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RCorfmanCommented:
You should note that the function split (the pipelined function) creates a table dynamically based on the data at the time you make the query. If you want the data to change and be updated, this is fine the way it is... unless you really need to index it.

You can create a 'view' over the pipelined function to make it referencable by a name over the long run.

I have an example here:
http://www.experts-exchange.com/Databases/Q_21726128.html

Note that you can change this from , to space without too much trouble if needed.
0
pbocanegraCommented:
CREATE OR REPLACE FUNCTION splitstr(pio_string   IN OUT VARCHAR2
                 ,pi_delimiter IN     VARCHAR2) RETURN VARCHAR2
IS
  v_dpos   NUMBER;
  v_dlen   NUMBER;
  v_slen   NUMBER;
  v_result VARCHAR2(2000);
BEGIN
  v_result := NULL;
  IF pio_string IS NOT NULL THEN
    v_dlen := LENGTH(pi_delimiter);
    v_slen := LENGTH(pio_string);
    v_dpos := INSTR(pio_string,pi_delimiter);
    IF v_dpos > 0 THEN
      v_result := SUBSTR(pio_string,1,v_dpos-1);
      pio_string := SUBSTR(pio_string,v_dpos+v_dlen,v_slen);
    ELSE
      v_result := pio_string;
      pio_string := NULL;
    END IF;
  END IF;
  RETURN v_result;
END splitstr;


CREATE OR REPLACE PROCEDURE Split_Procedure(CODIGO VARCHAR2,SPLIT_STRING VARCHAR2) IS
tmpVar NUMBER;
RETVAL VARCHAR2(200);
PIO_STRING VARCHAR2(200);
PI_DELIMITER VARCHAR2(1);
BEGIN
PIO_STRING := SPLIT_STRING;
PI_DELIMITER := ',';
WHILE PIO_STRING IS NOT NULL LOOP
  RetVal := PRES_FORM.Splitstr ( PIO_STRING, PI_DELIMITER );
  INSERT INTO TBLSPLIT_STRING(SESION,CODIGO_STR,SPLIT_VALUE) SELECT USERENV('SESSIONID'),CODIGO,RETVAL FROM DUAL;
  COMMIT;
END LOOP;  
END Split_Procedure;

In the first function "splitstr", send the split result into a different words. In the second procedure "Split_Procedure", insert the individual words into a table named tblsplit_string until the original string length zero.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RCorfmanCommented:
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.
0
jwright9Author Commented:
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.
0
pbocanegraCommented:
Sorry, My solution is for Oracle 8i.
0
geotigerCommented:
J.

I am glad that it works for you. It was my pleasure to help.

GT
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.