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 CF_Email_Validar (
					@Email VARCHAR(5000)
					) RETURNS TINYINT as
BEGIN     
/* 
	Devuelve 0 cuando el mail es válido, sino devuelve la regla que no pasa
*/

  DECLARE @bitEmailVal AS TINYINT
			,@EmailText VARCHAR(5000)
			,@Pos	INT
			,@Long INT
			,@CharAscii INT

  DECLARE	@Id INT ,   @ColumnA VARCHAR(MAX)


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


  SET @EmailText = REPLACE(@EmailText,';',',')

  DECLARE CurEMailSplit CURSOR FOR SELECT * FROM dbo.CF_Split(@EmailText,',')
  OPEN CurEMailSplit
  FETCH NEXT FROM CurEMailSplit INTO @Id,@ColumnA
  WHILE (@@FETCH_STATUS=0 AND @bitEmailVal = 0)
  BEGIN
		SET @bitEmailVal = CASE WHEN @ColumnA = '' THEN 1
								WHEN @ColumnA like '% %' THEN 2
								WHEN @ColumnA like ('%"(),:;<>\%') THEN 3
								WHEN SUBSTRING(@ColumnA,CHARINDEX('@',@ColumnA),len(@ColumnA)) like ('%!#$%&*+/=?^`_{|%') THEN 4
								WHEN (left(@ColumnA,1) like ('-_.+') or right(@ColumnA,1) like ('-_.+')) THEN 5                                                                                    
								--WHEN (@ColumnA like '%%' or @ColumnA like '%%') THEN 6
								WHEN @ColumnA LIKE '%@%@%' THEN 7
								WHEN @ColumnA NOT LIKE '_%@_%._%' THEN 8
								ELSE 0 
							END
		IF @bitEmailVal = 0
		BEGIN
			SET @Long = LEN(@EmailText) 
			SET @Pos = 1 
			WHILE  (@pos <= @Long)
			BEGIN
				SET @CharAscii = ASCII(SUBSTRING(@ColumnA,@Pos,1))
				IF NOT ( 
					(@CharAscii BETWEEN 97 AND 122 )  --   a-z
					OR (@CharAscii BETWEEN 48 AND 57 )   --   0-9
					OR (SUBSTRING(@ColumnA,@Pos,1) IN ('@','.','!','#','$','%','&','*','/','=','?','^','_','+','-','`','{','|','}','~'))
					)
				BEGIN
					SET @bitEmailVal =  9 
				END
				SET @Pos += 1
			END
		END
		FETCH NEXT FROM CurEMailSplit INTO @Id,@ColumnA
  END 
  CLOSE CurEMailSplit
  DEALLOCATE CurEMailSplit

  RETURN CONVERT(TINYINT,@bitEmailVal)
END 
