Re: how to seperate a string on a certain position of a character (e.g:semikolon) in PLSQL

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 4 Apr 2003 20:27:35 GMT
Message-ID: <b6kprm$6c6n5$1@ID-82536.news.dfncis.de>

> I have a string e.g:
>
> cc_people := 'adam.white_at_xxx.com;andrew.guest_at_xxx.com;jim.whatever_at_yyy.com'
> .....
>
> how can I get each individualls email address out of here e.g
>
> adam.while_at_xxx.com
> andrew.guest_at_xxx.com
> jim.whatever_at_yyy.com

I've written a function recently to split strings on one or more characters that you might find useful:

create or replace type table_of_vc as table of Varchar2(1000); /

create or replace Function SplitLine(

            p_line       in Varchar2,
            p_delim      in table_of_vc  default table_of_vc(' '),
            p_min_length in Number default 3) 
         return table_of_vc 
is
  v_delim_len      number;
  v_split_pos      number;
  v_split_from_pos number := 1;
  v_split_str      varchar2(100);
  v_ret            table_of_vc := table_of_vc();

begin

  begin
    select
      pos , len into v_split_pos, v_delim_len     from (

      select 
        len,
        pos,
        row_number () over (order by pos) r
      from (
        select 
          length(column_value)               len,
          instr(p_line,column_value,1)       pos
        from 
          table(p_delim)
        ) where pos > 0

    ) where r = 1;

  exception
    when no_data_found then
      v_split_pos := 0;
    when others then
      return v_ret;
  end;

  while v_split_pos > 0 loop

    v_split_str :=
      substr(p_line, v_split_from_pos, v_split_pos-v_split_from_pos);     if length(v_split_str) >= p_min_length then

      v_ret.extend;
      v_ret(v_ret.count) := v_split_str;

    end if;

    v_split_from_pos := v_split_pos + v_delim_len;

    begin

      select 
        pos , len into v_split_pos, v_delim_len
      from (
        select 
          len,
          pos,
          row_number () over (order by pos)    r
        from (
          select 
            length(column_value)                    len,
            instr(p_line,column_value,v_split_from_pos) pos
          from 
            table(p_delim)
          ) where pos > 0
      ) where r = 1;
    exception
      when no_data_found then
        v_split_pos := 0;

    end;

  end loop;

  v_split_str := substr(p_line,v_split_from_pos);   if length (v_split_str) >= p_min_length then     v_ret.extend;
    v_ret(v_ret.count) := v_split_str;
  end if;

  return v_ret;
end SplitLine;
/

Then, SplitLine can be used like this:

set serveroutput on size 100000

declare
  cc_people Varchar2(200)
    := 'adam.white_at_xxx.com;andrew.guest_at_xxx.com;jim.whatever_at_yyy.com'; begin
  for r in (select * from table

      (cast (SplitLine(cc_people,table_of_vc(';'),1) as table_of_vc))) loop     dbms_output.put_line(r.column_value);   end loop;
end;
/

If you need to split on more characters, for example on ";" and "," and " " , it would be called like this:

.... SplitLine(cc_people, table_of_vc(';',',',' '),1) ....

hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Fri Apr 04 2003 - 14:27:35 CST