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

image

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

4 comments:

Madhivanan said...

Suprotim

Isn't it possible to add code tag so that when we copy the code and paste in Query analyser they appear as they are instead of everything in a single line (which obviously need rework formatting the code)?

Madhivanan
http://beyondrelational.com/blogs/madhivanan

Suprotim Agarwal said...

Madhivanan,

Yes I know about this issue. Do you know of a plugin that lets you format code with Live Writer?

GeakeIT said...

I wrote a script a while ago that finds bad formats and human errors, then corrects them too.

Basically it cross references domains that have email opens recorded against them and corrects several human errors. So homail(missing character)/htomail(swapped character)/jotmail(close proximity keyboard button) are all corrected.

Details are too long to write here really so have a look at this link...

http://www.geakeit.co.uk/2010/07/30/the-ultimate-database-email-address-validatingprocessingcorrecting-script/

Anonymous said...

[Email] NOT LIKE '_%@__%.__%'

[Email] LIKE '_%@__%.__%'