IF object_Id('isValidEmailAddress') IS NOT NULL DROP FUNCTION isValidEmailAddress
GO

CREATE FUNCTION isValidEmailAddress (@email VARCHAR(300) = NULL) RETURNS BIT AS BEGIN

	-- Email address is not Null!
	IF @email IS NULL RETURN 0

	-- No spaces in email address
	IF (CHARINDEX(' ',LTRIM(RTRIM(@email))) <> 0) RETURN 0

	-- First character is not @
	IF (LEFT(LTRIM(@email),1) = '@') RETURN 0

	-- Doesn't end in .
	IF (RIGHT(RTRIM(@email),1) = '.') RETURN 0

	-- There isn't a . immediately after the @ symbol
	IF (CHARINDEX('.',@email,CHARINDEX('@',@email)) - CHARINDEX('@',@email) = 1) RETURN 0

	-- There is only one @ symbol
	IF (LEN(LTRIM(RTRIM(@email))) - LEN(REPLACE(LTRIM(RTRIM(@email)),'@','')) <> 1) RETURN 0

	-- There are at least 2 characters right of last period
	IF (CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email)))) < 3) RETURN 0

	-- There are no .@ or .. entries (so also no ... etc)
	IF (CHARINDEX('.@',@email) <> 0 OR CHARINDEX('..',@email) <> 0) RETURN 0

	-- Email passess format checks -- now have to check every character!
	DECLARE @recipientAllowed VARCHAR(150)
	DECLARE @domainAllowed VARCHAR(150)
	DECLARE @recipient VARCHAR(150)
	DECLARE @domain VARCHAR(150)

	-- Allowed Characters for the recipient and domain parts of the email address.
	SET @recipientAllowed = 'abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ._-'''
	SET @domainAllowed = 'abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ._-'

	-- Tables to hold the characters for both.
	DECLARE @recipientMap TABLE (symbol CHAR(1))
	DECLARE @domainMap TABLE (symbol CHAR(1))

	DECLARE @charpos INT
	DECLARE @chartop INT
	
	-- Populate the check tables..
	SET @charpos = 1
	SET @chartop = LEN(@recipientAllowed)
	WHILE (@charpos <= @chartop) BEGIN
		INSERT INTO @recipientMap (symbol) SELECT SUBSTRING(@recipientAllowed, @charpos, 1)
		SET @charpos = @charpos + 1
	END

	SET @charpos = 1
	SET @chartop = LEN(@domainAllowed)
	WHILE (@charpos <= @chartop) BEGIN
		INSERT INTO @domainMap (symbol) SELECT SUBSTRING(@domainAllowed, @charpos, 1)
		SET @charpos = @charpos + 1
	END

	-- Split the email address into recipient and domain parts
	SET @recipient = LEFT(@email, CHARINDEX('@', @email) - 1)
	SET @domain = RIGHT(@email, LEN(RTRIM(@email))-CHARINDEX('@',@email))

	-- Recipient Checks
	SET @charpos = 1
	SET @chartop = LEN(@recipient)
	WHILE (@charpos <= @chartop) BEGIN
		IF (SUBSTRING(@recipient, @charpos, 1) NOT IN (SELECT symbol FROM @recipientMap)) RETURN 0
		SET @charpos = @charpos + 1
	END

	-- Domain Checks
	SET @charpos = 1
	SET @chartop = LEN(@domain)
	WHILE (@charpos <= @chartop) BEGIN
		IF (SUBSTRING(@domain, @charpos, 1) NOT IN (SELECT symbol FROM @domainMap)) RETURN 0
		SET @charpos = @charpos + 1
	END

	-- OK if we get here than the email address must be OK!
	RETURN 1
END
GO