Re: Question: How to create this function

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 07 Jan 1999 22:59:36 GMT
Message-ID: <36962e53.21240372@inet16.us.oracle.com>

On 7 Jan 1999 03:35:04 GMT, "Ming Liu" <qinggu_at_worldnet.att.net> wrote:

>Hi. Folks:
>I want to create a function call get_item to do the following:
>get_item('A,B,C,D', 1, ',') = 'A'
>get_item('A,B,C,D', 2, ',') = 'B'
>get_item('A,B,C,D', 3, ',') = 'C', ect.
>Thanks for any hints.

How about this for a hint.

create or replace
package utils as

  procedure reset_occurrence;

  function get_item( p_str varchar2, p_delim varchar2 default ',' )     return varchar2;

end utils;
/

create or replace
package body utils as

  g_occurrence number := 0;

  procedure reset_occurrence is
  begin
    g_occurrence := 0;
  end reset_occurrence;

  function get_item( p_str varchar2, p_delim varchar2 default ',' )     return varchar2 is
    l_str long;
  begin

    if instr( p_str, p_delim, 1, g_occurrence+1 ) is null then       return null;

    elsif g_occurrence = 0 then
      l_str := substr( p_str, 1, instr( p_str, p_delim )-1 );

    elsif instr( p_str, p_delim, -1 ) =

       instr( p_str, p_delim, 1, g_occurrence ) then
      l_str := substr( p_str, instr( p_str, p_delim, -1 )+1 );

    elsif g_occurrence = 0 then
      l_str := substr( p_str, 1, instr( p_str, p_delim )-1 );

    else
      l_str := substr( p_str, 
                       instr( p_str, p_delim, 1, g_occurrence )+1,
                       instr( p_str, p_delim, 1, g_occurrence+1 ) -  
                       instr( p_str, p_delim, 1, g_occurrence )-1 );

    end if;

    g_occurrence := g_occurrence + 1;
    return l_str;
  end get_item;

end utils;
/

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jan 07 1999 - 16:59:36 CST