Showing posts with label Validate Email Id in sqlserver2008. Show all posts
Showing posts with label Validate Email Id in sqlserver2008. Show all posts

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 '..'

)