Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Question: How to create this function

Re: Question: How to create this function

From: Alexander I. Doroshko <aid_at_grant.kharkov.ua>
Date: 12 Jan 1999 19:12:32 GMT
Message-ID: <01be3e5f$660a0320$190114c1@sister.grant.UUCP>


A bit simpler, if you want to find a delimited substring.

function get_item( p_str varchar2, g_occurence number, p_delim varchar2 default ',' ) return varchar2 is
  i_beg number;
begin
 i_beg := instr(p_delim||p_str, p_delim, 1, g_occurence);  return substr(p_str, i_beg,
   instr(p_str||p_delim, p_delim, i_beg, 1)-i_beg); end get_item;

If you want to break the string into substrings, you are better to use a procedure sort of dbms_utilities.comma_to_table. ---
 Alexander I.Doroshko, aid_at_grant.kharkov.ua

Christopher Beck <clbeck_at_us.oracle.com> wrote in article <36962e53.21240372_at_inet16.us.oracle.com>...
: On 7 Jan 1999 03:35:04 GMT, "Ming Liu" <qinggu_at_worldnet.att.net>
: wrote:
: >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.

[skipped]
: 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 );
:

[ 7 more source lines skipped]
: end if;
:
: g_occurrence := g_occurrence + 1;
: return l_str;
: end get_item;
Received on Tue Jan 12 1999 - 13:12:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US