Validate Email Addresses in SQL Server

I will be honest here – doing email address validation in SQL Server is one of the most challenging tasks a T-SQL programmer faces.

Stefan Plattner sometime back on the forums shared a query to validate Email Address. I have been using this query in my applications and thought of sharing this with all of you

Let us create a sample table

DECLARE @TT table
(
ID smallint,
StudentID smallint,
EA nvarchar(30)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1,  'jill@abcdef.com');
INSERT INTO @TT VALUES ( 102, 2,  'matt@abcdef.com');
INSERT INTO @TT VALUES ( 103, 3,  'abcdef.com');
INSERT INTO @TT VALUES ( 104, 4,  '@abcdef@deamon.com');
INSERT INTO @TT VALUES ( 105, 5,  'carlos@abcdef.com');
INSERT INTO @TT VALUES ( 106, 6,  'sachin@abcdef.com');
INSERT INTO @TT VALUES ( 107, 7,  'terri.jake@abcdef.com');
INSERT INTO @TT VALUES ( 108, 8,  'triplet@@abcdef.com');
INSERT INTO @TT VALUES ( 109, 9,  'robert@abcdef.com');
INSERT INTO @TT VALUES ( 110, 10, 'bill@abcdef.com');

Now write the query to print valid email addresses

-- Print Valid Email Addresses
SELECT * FROM @TT WHERE
CHARINDEX(' ',LTRIM(RTRIM(EA))) = 0
AND  LEFT(LTRIM(EA),1) <> '@'
AND  RIGHT(RTRIM(EA),1) <> '.'
AND  CHARINDEX('.',EA , CHARINDEX('@',EA))- CHARINDEX('@',EA ) > 1
AND  LEN(LTRIM(RTRIM(EA )))- LEN(REPLACE(LTRIM(RTRIM(EA)),'@','')) = 1
AND  CHARINDEX('.',REVERSE(LTRIM(RTRIM(EA)))) >= 3
AND  (CHARINDEX('.@',EA ) = 0
AND CHARINDEX('..',EA ) = 0)

OUTPUT

As you can see the query eliminates email addresses of ID 103, 104 and 108 since they are invalid.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook