Working with email addresses in SQL Server

Below is an example of adding email address constraint in the database field.

ALTER TABLE [dbo].[Subscribers]
WITH CHECK ADD
CONSTRAINT [EmailValidator]
CHECK
(
CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 -- No embedded spaces in email
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 '..'
)
GO

To check invalid email address in the database field, use the code below.


SELECT * FROM Subscribers
WHERE NOT
(
CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0
AND LEFT(LTRIM([Email]),1) <> '@'
AND RIGHT(RTRIM([Email]),1) <> '.'
AND CHARINDEX('.',[Email],CHARINDEX('@',[Email])) - CHARINDEX('@',[Email]) > 1
AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3
AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0)
)

Reference:
http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm

Advertisements

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: