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.