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.
精彩评论