Ok, doing some hired gun work. The client wants the record set returned from a query to have only valid e-mails.
The gist includes fully working CFML that demonstrates this example.
The core of this is the isValid function. In this case it is used to validate an e-mail address
isValid('email', email) email, the second one, is a filed in the table.
The code also adds a column to the record set with QueryAddColumn and then does a query of queries to get only valid e-mail addresses
Some good comments, if you use underscore or mySQL...
Thanks Russ & Timothy Leach
SELECT `email`
FROM `users`
WHERE `email`
REGEXP '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}'
GROUP BY `email`
SQL Server would have to be something like:
WHERE email like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%'
4 comments:
Interesting problem! I was able to solve it a little differently using functional-style programming: https://gist.github.com/russplaysguitar/5530257
If you're using mySQL you can just do this:
SELECT `email`
FROM `users`
WHERE `email`
REGEXP '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}'
GROUP BY `email`
SQL Server would have to be something like:
WHERE email like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%'
Great Comments! Thanks! Russ, mind if I post your code?
Go right ahead Rich!
Post a Comment