Using table-valued functions (TVF) in SQL Server
I was looking for more information on Table-Valued Functions (TVF) in SQL Server in attempting to perform sophisticated text analysis using the built-in string functions using .NET.
To the subject using regular expressions are not new to SQL. These regular expressions could actually be used in earlier versions of SQL Server, but the process was inefficient. Such as using the sp_OACreate stored procedure, any OLE automation object that implemented regular expressions could be used, but you had to create a COM object first, then make at least one IDispatch call, then destroy the object. This way you are consuming the memory resources all the times and in previous SQL versions this could lead to a server crash with fatal errors.
Within SQL 2005 now there's SQLCLR (good one), a CLR user-defined function (UDF) that lets you create an efficient and less error-prone set of functions using the .NET framework, easy isn't it. CLR user-defined functions are simply static methods (shared functions in VB & VC) defined within a .NET assembly.
Fyi, in addition to the SQL 2005 BOL I have been referring to this TVFBlog too.