• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 23526
  • Last Modified:

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.
0
jwright9
Asked:
jwright9
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
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
 
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now