Friday, June 24, 2011

Working with Email Address in SQL Server ( Validate Email Address )

at times working with email addresses in sql server becomes difficult so
here is a UDF you can use to validate the email address

It will validate mentioned below list

No Spaces are allowed
email cant start with '@'
email cant end at '.'
@must be in email and only once
domain name should be at the end and must be at least two characters
email cant habe patterns like '..' or '.@'








CREATE FUNCTION [dbo].[validateEmailAddress]
(
@EmailAddress nVARCHAR(4000)
)
RETURNS TINYINT AS
BEGIN


DECLARE @Result TINYINT
SELECT @Result =
CASE WHEN
CHARINDEX(' ',LTRIM(RTRIM(@EmailAddress))) = 0
AND LEFT(LTRIM(@EmailAddress),1) <> '@'
AND RIGHT(RTRIM(@EmailAddress),1) <> '.'
AND CHARINDEX('.',@EmailAddress,CHARINDEX('@',@EmailAddress)) - CHARINDEX('@',@EmailAddress) > 1
AND LEN(LTRIM(RTRIM(@EmailAddress))) - LEN(REPLACE(LTRIM(RTRIM(@EmailAddress)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@EmailAddress)))) >= 3
AND (CHARINDEX('.@',@EmailAddress) = 0 AND CHARINDEX('..',@EmailAddress) = 0)
THEN 1 ELSE  0 END

RETURN @Result

END



Example:- 

it will return 1 for correct email address and 0 for incorrect 
:
SELECT  dbo.validateEmailAddress('myemail@mydomain.com')  -- Correct

SELECT  dbo.validateEmailAddress('@myemail@m  domain.com')  -- Incorrect