Apr 6, 2012

Validate Email Id in sqlserver2008

ALTER TABLE UserRegistration
WITH CHECK ADD
CONSTRAINT chkUserEmail
CHECK
(
CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 -- No embedded spaces
AND CHARINDEX('''', Email)=0
AND charindex('(', Email)=0 AND charindex(')', Email)=0
AND LEFT(LTRIM([Email]),1) <> '@' -- '@' can't be the first character of an email address
AND RIGHT(RTRIM([Email]),1) <> '.' -- '.' can't be the last character of an email address
AND CHARINDEX('.',[Email],CHARINDEX('@',[Email])) - CHARINDEX('@',[Email]) > 1 -- There must be a '.' after '@'
AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1 -- Only one '@' sign is allowed
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3 -- Domain name should end with at least 2 character extension
AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0) -- can't have patterns like '.@' and '..'

)

No comments:

Post a Comment