Re: how do I check a mail-adres?

From: <bsc7080mqc_at_mylxhq.com>
Date: Sat, 17 Apr 2004 10:12:49 -0400
Message-ID: <vme280h44l1h6ipsevqjhht89pi6t8mslq@4ax.com>

Here is a function I built a few years ago after reviewing the RFC specs on email address formatting. Bear in mind, that it will still not tell you if the address is real or not. There are UTL_TCP commands you could throw at the server to determine if the address is a valid domain or not, but I am not sure what you could do to tell if it is a real and honest to goodness address prior to sending :

FUNCTION VALIDATE_EMAIL (v_STRING_IN IN VARCHAR2 DEFAULT NULL)

   RETURN VARCHAR2
IS

	v_STATUS	CHAR(1) := 'Y'; -- EMAIL IS VALID BY DEFAULT
	v_STRING	VARCHAR2(100) :=

ltrim(rtrim(lower(v_STRING_IN))); -- Remove spaces and dropcase BEGIN
IF v_STRING is NULL THEN -- Verify email is not null  v_STATUS := 'N';
ELSIF NVL(length(v_STRING),0) <= 4 THEN -- Verify length, a_at_b.c is the shortest possible (5 characters)
 v_STATUS := 'N';
ELSIF v_STRING not like '%@%' THEN -- Verify presence of at least one @
 v_STATUS := 'N';
ELSIF NVL(length(substr(v_STRING,(instr(v_STRING,'.',-1)+1))),0) >= 4 THEN -- Verify domain extension not greater than 3 .com .net .org  v_STATUS := 'N';
ELSIF instr(v_STRING,'_') <> 0 and
NVL(instr(substr(v_STRING,instr(v_STRING,'@',-1)+1),'_'),0) <> 0 THEN -- Verify no underscore to the right of @  v_STATUS := 'N';
ELSIF substr(v_STRING,instr(v_STRING,'@',-1)+1) not like '%.%' THEN -- Verify period to the right of @
 v_STATUS := 'N';
ELSIF NVL(length(substr(v_STRING,1,instr(v_STRING,'@',-1)-1)),0) = 0 THEN -- Verify text to the left of @
 v_STATUS := 'N';
ELSIF NVL(length(substr(v_STRING,instr(v_STRING,'@',-1)+1)),0) = 0 THEN -- Verify text to the right of @
 v_STATUS := 'N';
END IF; RETURN (v_STATUS);

EXCEPTION
    WHEN OTHERS
    THEN
        RETURN 'N';
END; Received on Sat Apr 17 2004 - 09:12:49 CDT