Create a function
How can I turn this select statement into a function that will return true if the email address appears valid and false if it doesn't. This select statement works, but only returns bad email addresses.
SELECT * FROM ARCUSFIL_SQL WHERE NOT (
CHARINDEX(' ',LTRIM(RTRIM([Email_addr]))) = 0 AND
LEFT(LTRIM([Email_addr]),1) <> '@' AND
RIGHT(RTRIM([Email_addr]),1) <> '.' AND
CHARINDEX('.',[Email_addr],CHARINDEX('@',[Email_addr])) - CHARINDEX('@',[Email_addr]) > 1 AND
LEN(LTRIM(RTRIM([Email_addr]))) - LEN(REPLACE(LTRIM(RTRIM([Email_addr])),'@','')) = 1 AND
CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email_addr])))) >= 3 AND
(CHARINDEX('.@',[Email_addr]) = 0 AND CHARINDEX('..',[Email_addr]) = 0) )
QPR
Have you pasted the right bit? In the right forum?
Here is the syntax for creating a function
http://msdn.microsoft.com/en-us/library/ms186755.aspx
Look at the "return" keyword - this will allow you to return true/false based upon your select statement within the function
http://msdn.microsoft.com/en-us/library/ms186755.aspx
Look at the "return" keyword - this will allow you to return true/false based upon your select statement within the function
someones done one here
http://www.nimbleuser.com/blogs/Developers/developers/SQL_Function_to_Validate_email_address_syntax.aspx
It's basic but might do the trick if needed to be bulletproof
Failing that, google: t-sql validate email address
Bound to be heaps
http://www.nimbleuser.com/blogs/Developers/developers/SQL_Function_to_Validate_email_address_syntax.aspx
It's basic but might do the trick if needed to be bulletproof
Failing that, google: t-sql validate email address
Bound to be heaps
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works perfectly! Thanks!