Regular Expressions are an extremely powerful tool for parsing and querying textual data; so using them in a database environment looks like a good match. But DBMS vendors have been rather slow building RegEx functionality into their offerings. Oracle has it since 10g and some open source databases also have a head start.
Microsoft obviously put the emphasis on XML/XPath since SQL Server 2000, and regular expression support is not on the horizon, even for SQL Server 2008.
A MSDN article gives you all you need to get you started with regular expressions in SQL Server 2005. Be sure to download the source code which includes a sample project. You have to set the database connection in the project properties and then start debugging. The assembly will then be automatically deployed to the server and you'll have regular expression support via CLR user-defined functions. You must have the compatibility level set at 90 (SQL Server 2005), so you better be absolutely sure that sp_dbcmptlevel won't break your stored procedures. Good luck!