Re:Parse through a string in PL/SQL

From: <dgoulet_at_vicr.com>
Date: Mon, 27 Nov 2000 17:11:59 -0500
Message-Id: <10693.123012@fatcity.com>

Helmut,

    Try this:

procedure extract_next(str in varchar2, position in out number, result out varchar2) is

   a number;
  begin
   a := instr(substr(str,position),',',1);    if(a = 0) then a := length(substr(str, position));    end if;
   result := substr(str, position, instr(substr(str,position),',',1)-1);    a := instr(substr(str,position),',',1);    if(a = 0) then position := length(str)+1;    else position := position + a;
   end if;
  end;

____________________Reply Separator____________________

Subject: Parse through a string in PL/SQL Author: "Helmut Daiminger" <hdaiminger_at_vivonet.com> Date: 11/27/2000 1:47 PM

Hi!

How can I parse through a string in PL/SQL and assign different parts of the string to variables?

E.g. given string: "Jeff,Volberg,500 Oracle Parkway"

How can I go through the string and assign "Jeff" to the variable first_name
(i.e. from the beginning to the first comma)?
Then continue after the first comma (until I hit the next comma) and assign that part of the string (Volberg) to the variable last_name. And finally assign the text after the last comma (until the end of the input string) to the variable address?

Any idea?

Thanks,
Helmut

PS: This is 8.1.6 on Win2k.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Helmut Daiminger
  INET: hdaiminger_at_vivonet.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Mon Nov 27 2000 - 16:11:59 CST