T-SQL: checking for email format

Question

I have this scenario where I need data integrity in the physical database. For example, I have a variable of @email_address VARCHAR(200) and I want to check if the value of @email_address is of email format. Anyone has any idea how to check format in T-SQL?

Many thanks!

Answer 1

I tested the following query with many different wrong and valid email addresses. It should do the job.

IF (
     CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0 
AND  LEFT(LTRIM(@email_address),1) <> '@' 
AND  RIGHT(RTRIM(@email_address),1) <> '.' 
AND  CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1 
AND  LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1 
AND  CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3 
AND  (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)
)
   print 'valid email address'
ELSE
   print 'not valid'

It checks these conditions:

• No embedded spaces
• '@' can't be the first character of an email address
• '.' can't be the last character of an email address
• There must be a '.' somewhere after '@'
• the '@' sign is allowed
• Domain name should end with at least 2 character extension
• can't have patterns like '.@' and '..'

Answer 2

AFAIK there is no good way to do this.

The email format standard is so complex parsers have been known to run to thousands of lines of code, but even if you were to use a simpler form which would fail some obscure but valid addresses you'd have to do it without regular expressions which are not natively supported by T-SQL (again, I'm not 100% on that), leaving you with a simple fallback of somethign like:

LIKE '%_@_%_.__%'

..or similar.

My feeling is generally that you shouln't be doing this at the last possible moment though (as you insert into a DB) you should be doing it at the first opportunity and/or a common gateway (the controller which actually makes the SQL insert request), where incidentally you would have the advantage of regex, and possibly even a library which does the "real" validation for you.