IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'CF_Email_Validar') 
                   and type = 'FN')
BEGIN
    DROP FUNCTION CF_Email_Validar
END
GO

CREATE FUNCTION [dbo].[CF_Email_Validar](@EMAIL varchar(100)) RETURNS TINYINT as
BEGIN     
/* Devuelve 1 cuando el mail es válido*/

  DECLARE @bitEmailVal AS BIT
			,@EmailText VARCHAR(100)
			,@Pos	INT
			,@Long INT
			,@CharAscii INT


  SET @EmailText=LOWER(LTRIM(RTRIM(ISNULL(@EMAIL,''))))

  SET @bitEmailVal = CASE WHEN @EmailText = '' THEN 0
                          WHEN @EmailText like '% %' THEN 0
                          WHEN @EmailText like ('%["(),:;<>\]%') THEN 0
                          WHEN SUBSTRING(@EmailText,CHARINDEX('@',@EmailText),len(@EmailText)) like ('%[!#$%&*+/=?^`_{|]%') THEN 0
                          WHEN (left(@EmailText,1) like ('[-_.+]') or right(@EmailText,1) like ('[-_.+]')) THEN 0                                                                                    
                          WHEN (@EmailText like '%[%' or @EmailText like '%]%') THEN 0
                          WHEN @EmailText LIKE '%@%@%' THEN 0
                          WHEN @EmailText NOT LIKE '_%@_%._%' THEN 0
                          ELSE 1 
                      END
  IF @bitEmailVal = 1
  BEGIN
	  SET @Long = LEN(@EmailText) 
	  SET @Pos = 1 
	  WHILE  (@pos <= @Long)
	  BEGIN
			SET @CharAscii = ASCII(SUBSTRING(@EmailText,@Pos,1))
			IF NOT ( 
				(@CharAscii BETWEEN 97 AND 122 )  --   a-z
				OR (@CharAscii BETWEEN 48 AND 57 )   --   0-9
				OR (SUBSTRING(@EmailText,@Pos,1) IN ('@','.','!','#','$','%','&','*','/','=','?','^','_','+','-','`','{','|','}','~'))
				)
			BEGIN
				SET @bitEmailVal =  0 
			END
			SET @Pos += 1
	  END
  END



  RETURN CONVERT(TINYINT,@bitEmailVal)
END 
