Re: Request PL/SQL Help

From: <mhr123_at_my-deja.com>
Date: Wed, 24 Jan 2001 09:41:06 GMT
Message-ID: <94m7vh$mjj$1@nnrp1.deja.com>

In article <Wnqb6.8077$1m.487023_at_bgtnsc04-news.ops.worldnet.att.net>,   "Buck Turgidson" <remove-me_jc_va_at_hotmail.com> wrote:
> 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
>
>

Hi again,

Thinking and guessing a little further :)

Here is a function which returns the FIRST occurence of a mail adress of the domain_in domain:

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);  string := substr(email_in,1,end_of_address-1);  beg_of_address := INSTR(string,',',-1)+1;  RETURN SUBSTR(string,beg_of_address,end_of_address - beg_of_address); EXCEPTION
 WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
/

Is this what you want?

Regards, Michael Ringbo

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 24 2001 - 03:41:06 CST