>I have a table with a field called email.
>I would like to make sure that all email are valid.
>How can I build a select statement that will return all the records that do not have a .com or .net in the email field.
>
>Select * from emails WHERE ".com" OR ".net" is not in the email field
>
>Any help is appreciated.
>
>Thanks

Sergio,
Validating such data are better done with regular expressions. T-SQL doesn't directly support regular expression search but you can create a .net udf in SQL 2005 and later that does it for you. Or even you can create an xp in older versions. Or you can use regular expression search client side.

(In the first link interestingly enough email is used to test the function:)

http://weblogs.asp.net/farhank/archive/2008/07/04/creating-clr-sql-user-defined-function-to-validate-values-using-regular-expression.aspx
http://www.codeproject.com/KB/database/xp_pcre.aspx?df=100&forumid=16452&exp=0&select=1191266
http://www.codeproject.com/KB/mcpp/xpregex.aspx

If you would do that client side then in VFP the code is like:

LOCAL loRegEx
loRegEx = Createobject("VBScript.RegExp")
loRegEx.Pattern = "\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
loRegEx.ignoreCase = .T.
loRegEx.Global = .T.


SELECT * FROM myCursor WHERE !loRegEx.test(email)

Cetin