Request PL/SQL Help

From: Buck Turgidson <remove-me_jc_va_at_hotmail.com>
Date: Wed, 24 Jan 2001 01:40:06 GMT
Message-ID: <Wnqb6.8077$1m.487023@bgtnsc04-news.ops.worldnet.att.net>

I am trying to parse a string that contains an email address of a certain domain, say "abc.com". The string could contain many addresses, and in any order. I have written a PL/SQL program to do it, but it's not working quite right. Can someone spot my error? Here is the PL/SQL, and some test data.

CREATE OR REPLACE FUNCTION getourmail( email_in IN TEST.EMAIL_ADDR%TYPE,domain_in IN VARCHAR2)   RETURN VARCHAR2
IS

  end_of_address NUMBER := 0;
  beg_of_address NUMBER := 0;
  string VARCHAR2(1000) := email_in;

BEGIN
  end_of_address := INSTR(email_in,domain_in,1) + LENGTH(domain_in) - 1;   string := substr(email_in,1,end_of_address);   beg_of_address := INSTR(string,',',-end_of_address,1);   RETURN SUBSTR(string,beg_of_address,end_of_address - beg_of_address); EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN NULL;
END; create table test
(

email_addr varchar2(80)
);

insert into test values
('jim_at_abc.com,jim_at_pqr.com');

insert into test values
( 'jim_at_pqr.com,jim_at_abc.com');

insert into test values
( 'jim_at_pqr.com,jim_at_abc.com,jim_at_tuv.com');

commit;

select email_addr,getourmail(email_addr,'abc.com') from test Received on Tue Jan 23 2001 - 19:40:06 CST