Friday 21 November 2014

Email validation at Database with check constraint

 create table t_superheros (   id   int  , name    nvarchar(14), [Email]   nvarchar(20) CONSTRAINT [EmailValidator]
CHECK
(
CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 -- No embedded spaces
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