Re: Need a way to split a varchar

> I am looking for a function, or some other way to split my variable
> type varchar. The way i would like to do it is to , cut the first word
> from the variable, place it in the database, along with the id of that
> word. Then move to the next word. Im using PL?SQL. Here is what i have
> so far...
>
>
> DECLARE
> auto_key NUMBER(11);
>
> objective_clob_size NUMBER(10);
> objective_clob CLOB;
> objective_varchar2 VARCHAR2(1000);
>
> BEGIN
>
> auto_key := 1;
>
> SELECT objectives INTO objective_clob FROM project
> WHERE jobordernumber = 'DAML0001';
>
> objective_clob_size := dbms_lob.getlength(objective_clob);
> dbms_lob.read(objective_clob, objective_clob_size, 1,
> objective_varchar2);
> objective_varchar2 := TRIM(objective_clob);
>
> INSERT INTO list(id, word)
> VALUES(auto_key, objective_varchar2);
> COMMIT;
> END;
You can use regular expressions if you're on 10g:

create or replace type t_vc2_50 as table of varchar2(50); /

create or replace function split_vc2(str in varchar2) return t_vc2_50 as   ret t_vc2_50 := t_vc2_50();
  i number := 1;
  part varchar2(50);
begin

  loop     

    part := regexp_substr(str, '([^[:blank:]]+)', 1, i);

    exit when part is null;

    ret.extend;
    ret(i) := part;
    i := i+1;

  end loop;

  return ret;   

end split_vc2;
/

create table list (
  id number primary key,
  word varchar2(50)
);

declare
  objective_varchar2 varchar2(1000);
begin
  objective_varchar2 := 'Hello world, here''s something 4 u';

  insert into list
  select rownum, column_value
    from table(split_vc2(objective_varchar2)); end;
/

select * from list;

        ID WORD

---------- --------------------------------------------------
         1 Hello
         2 world,
         3 here's
         4 something
         5 4
         6 u

If you're not using 10g, see my function SplitLine at http://www.adp-gmbh.ch/ora/plsql/poor_mans_text_index.html

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/