Posted by --CELKO-- on 11/13/06 13:56

>> I know the things that can be validated using basic pattern matching .. what do you have in mind that can only be done via CLR? <<

Google for a previous posting in which I outlined a truly brutal query
technique that simulates a full regular expression parser in pure SQL
(+ and * were done with look-up tables to build the limited patterns
available in T-SQL).

I got an email from a friend who has written grep() from scratch in his
youth that he thinks he can do it with a recursive CTE. He is a DB2
user (they are up to Standards), so he does not need it, but it is a
good exercise. I will see if he comes up with anything when I get back
from PASS.

I keep saying this, but SQL Server needs to come up to Standard and
have a SIMILAR TO predicate like other products. Regular Expressions
are just too handy for validation.

>> I assume we can agree that verification (e.g. can I send mail to this email address) is out of scope. <<

In practice, that is the most important point; verification is stronger
than validation. People actually make few errors in the syntax of an
email address, but do make typos in the text of the email address.
Thus a simple pattern match for at-sign, proper suffixes and common
email providers validates better than 99.9% of the actual cases.

The other statistic I want to find is how long people keep the same
email address. I have heard 5+ years. The reason is that people tend
to keep an account with a provider and not move around. Yyou have your
monthly payment set up as an automatic withdrawal, everyone knows this
email, etc. -- same thing happened with land line phones

This is unlike cell phones where people shop for a good deal at the end
of each contract.

[Back to original message]