Regular expressions is a very powerful way to match data, far more powerful
than the LIKE operator in SQL. Regular expressions comes in a number of flavours:
POSIX defines one standard, .Net have their own twist (I think), and Perl is
probably the most elaborate of them all. But they all share some common
traits: . is a wildchard charcted, * means previous pattern zero or more times etc.
ANSI/ISO have tried to catch up, and SQL:1999 introduced the SIMILAR TO
operator, but it's obvious that they are on the wrong track. They still use % and
_ as wildcard which is simply akward and non-standard. They also insist on
the string matching the pattern entirely which also is non-standard.
Today you can use regpexps through the CLR routines, but this is common
enough to warrant a place directly in T-SQL. Also, that makes it difficult
to use indexes with regexps when you do an anchored search.