Best IT Recipes

T-SQL: checking email format

I have a scenario where I need data integrity in a physical database. For example, I have a variable @email_address VARCHAR(200) , and I want to check if @email_address to @email_address format. Does anyone know how to check format in T-SQL?

Thank you very much!

+6
sql email sql-server tsql
Jan 08 '09 at 8:46
source share
4 answers

I tested the following query with many different invalid and valid email addresses. He has to do this work.

 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'

He checks these conditions:

  • No built-in spaces
  • '@' cannot be the first character of an email address
  • '' cannot be the last character of an email address
  • It must be. somewhere after '@'
  • '@' is allowed
  • The domain name must end with at least 2 character extensions
  • cannot have patterns such as ". @" and ".."
+17
Jan 08 '09 at 9:04
source share

AFAIK there is no good way to do this.

The email format standard is that parsers that are so complex are known to work with thousands of lines of code, but even if you have to use a simpler form that will crash some obscure but valid addresses that you would have to do without regular expressions, which T-SQL is not initially supported (again, I'm not 100% at that), leaving you with a simple reserve somethign like:

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

.. or similar.

My feeling, as a rule, you do not do this at the last possible moment (although you insert it into the database), you should do it at the first opportunity and / or a common gateway (the controller that actually makes the SQL insert query), where, by the way, you will have the advantage of regex, and perhaps even a library that does a “real” check for you.

+8
Jan 08 '09 at 9:04
source share

If you are using SQL 2005 or 2008, you may want to write CLR stored prospectuses and use the .NET regex engine like this . If you are using SQL 2000 or earlier, you can use the VBScript like ths script engine regex . You can also use an extended stored procedure like this

+1
Jan 08 '09 at 9:03
source share

There is no easy way to do this in T-SQL, I'm afraid. To check for all kinds of email addresses permitted by RFC 2822 , you will need to use a regular expression.

More details here .

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

+1
Jan 08 '09 at 9:05
source share


More articles:


All Articles