RE: Parse through a string in PL/SQL

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Mon, 27 Nov 2000 17:16:28 -0500
Message-Id: <10693.123013@fatcity.com>

Sounds like you're looking for an implementation of the split() function from Perl. I have a bare-bones implementation if you'd like, contact me backchannel. ;-)

Really, though, this is pretty easy.

create or replace procedure parse_person (str in varchar2, first_name out varchar2, last_name out varchar2, address out varchar2) is

	i	number;
	j	number;
begin
	i := instr(str, ',', 1, 1);
	j := instr(str, ',', 1, 2);
	first_name := substr(str, 1, i-1);
	last_name := substr(str, i+1, j-i-1);
	address := substr(str, j+1);

end parse_person;
/

-----Original Message-----
From: Helmut Daiminger [mailto:hdaiminger_at_vivonet.com] Sent: Monday, November 27, 2000 4:47 PM
To: Multiple recipients of list ORACLE-L Subject: Parse through a string in PL/SQL

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:16:28 CST