SQL Server Email Validation Function

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[IsEmailValid] (@Email varchar (100))       

RETURNS BIT       

AS       

BEGIN       

 /*       

  One instance of @, multiple dots, atleast one after @       

  no space       

  after the last dot minimum 2 chars, max 3 chars       

  some chars between @ and .       

  No Special Chars       

    */       

         

 DECLARE @atpos int, @dotpos int       

       

 SET @Email = LTRIM(RTRIM(@Email)) -- remove leading and trailing blanks       

       

 IF LEN(@Email) = 0 RETURN(0) -- nothing to validate       

 SET @atpos = charindex('@',@Email) -- position of first (hopefully only) @       

 IF @atpos <= 1 OR @atpos = LEN(@Email) RETURN(0) -- @ is neither 1st or last or missing       

 IF CHARINDEX('@', @email, @atpos+1) > 0 RETURN(0) -- Two @s are illegal       

 IF CHARINDEX(' ',@Email) > 0 RETURN(0) -- Embedded blanks are illegal       

 SET @dotpos = CHARINDEX('.',Reverse(@Email)) -- location (from rear) of last dot       

 IF (@dotpos < 3) or (@dotpos > 5) or (LEN(@Email) - @dotpos) < @atpos RETURN (0) -- dot / 2 or 3 char, after @       

 if (@atpos + @dotpos = len(@email)) Return (0) -- Nothing between @ and .       

       

 -- Special Characters Scan       

 Declare @emaillen int, @currpos int, @currascii int       

       

 SET @emaillen = LEN(@email)       

 SET @currpos = 0       

 WHILE @emaillen != @currpos       

 BEGIN       

  SET @currpos = @currpos + 1       

  SET @currascii = ASCII(substring(@email, @currpos, 1))       

       

  if @currascii <= 44 and @currascii<>43 Return (0)       

  if @currascii = 47  Return (0)       

  if @currascii >= 58 and @currascii <= 63 Return (0)       

  if @currascii >= 91 and @currascii <= 94 Return (0)       

  if @currascii = 96 Return (0)       

  if @currascii >= 123 Return (0)       

 END       

 RETURN(1) -- Hope this is a valid email        

END

GO