SQL table column with email address

I have a table ‘custom开发者_如何学编程ers’ with two columns 'email' and ‘value’. The ‘email’ column contains email addresses and ‘value’ column contains NULL values. Now I would like to write an Update command, so that if an email is in right format (a@b.c) then SET the ‘value’ column with ‘True’ and if the email address is in wrong format (a.b.c Or @b.c) then SET the ‘value’ column with ‘False’. Now my question is how I shall write the SQL command to find out which email address is right. Any suggestion please. Thanks.

---

If you are using SQL Server 2005 or newer I would recommend creating a CLR function that checks for the validity of your emails. Take a look at this article to understand how this is done: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Once you have a function find a regex for your email address (attempt below) and use an update statement like this:

UPDATE Customers
SET Value = dbo.RegexMatch( email , N'^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$' )

---

If you use SQL-Server this example (taken from here) could help you.

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'

I think that changing print code with an UPDATE... will do your job.

---

the code to determine that is much better off the SQL side, write that in whichever language you're using to get those email addresses, then just perform the update using a simple query

---

you'll be a lot better off doing that in the front end, and passing the validation result to the database along with the email. This seems like a good case for a regex match.

---

find out which email address is right

While you can to validate in the database values that fail to fits the correct pattern for an email address, the database cannot verify whether an email address can be used to contact its intended recipient (the Closed World Assumption applies).

Support for pattern matching varies between SQL implementations. For example, SQL Server has LIKE for simplistic pattern matching. Best to write multiple tests for specific failures: does not contain exactly one @ symbol, has no domain, is too long, contains a character other than those allowed, etc. By giving each test a unique constraint name, more granular error messages can be conveyed to the user.