Re: Email Validation

From: <bsc7080mqc_at_mylxhq.com>
Date: Thu, 17 Apr 2003 04:04:49 -0400
Message-ID: <H3tna.22034$6v1.18912_at_fe10.atl2.webusenet.com>

[Quoted] As promised earlier... here is what I use to validate a correctly formatted email... suggestions or improvements are welcome :

CREATE OR REPLACE 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 '%_at_%' THEN -- Verify presence of at least one _at_
 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,'_at_',-1)+1),'_'),0) <> 0 THEN -- Verify no underscore to the right of _at_  v_STATUS := 'N';
ELSIF substr(v_STRING,instr(v_STRING,'_at_',-1)+1) not like '%.%' THEN -- Verify period to the right of _at_
 v_STATUS := 'N';
ELSIF NVL(length(substr(v_STRING,1,instr(v_STRING,'_at_',-1)-1)),0) = 0 THEN -- Verify text to the left of _at_
 v_STATUS := 'N';
ELSIF NVL(length(substr(v_STRING,instr(v_STRING,'_at_',-1)+1)),0) = 0 THEN -- Verify text to the right of _at_  v_STATUS := 'N';
END IF; RETURN (v_STATUS);

EXCEPTION
    WHEN OTHERS
    THEN
        RETURN 'N';
END;
/
MylxHQ - Oracle Resource Portal
http://oracle.mylxhq.com Received on Thu Apr 17 2003 - 10:04:49 CEST