T-SQL: checking for email format

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!

Solution 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 '..'

Solution 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.

Solution 3:

If you use SQL 2005 or 2008 you might want to look at writing CLR stored proceudues and use the .NET regex engine like this. If you're using SQL 2000 or earlier you can use the VBScript scripting engine's regular expression like ths. You could also use an extended stored procedure like this

Solution 4:

There is no easy way to do it in T-SQL, I am afraid. To validate all the varieties of email address allowed byRFC 2822 you will need to use a regular expression.

More info here.

You will need to define your scope, if you want to simplify it.