30. January 2011 14:36
MS SQL Support functions that can be created in C# or VB.NET but it needs to be turned on in order to map the functions. This is really quite easy to do.
Simply runt he following sql to enable CLR support.
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
Now that it has CLR support turned on you can bring in your own custom function from dll files created from Visual Studio. Using sql like the following
CREATE ASSEMBLY SqlExtensions
You can then create a function on a data for use like the following.
CREATE Function RegEx(@Input nvarchar(MAX), @Pattern nvarchar(MAX))
RETURNS BIT AS EXTERNAL NAME SqlExtensions.UserDefinedFunctions.RegEx
This can then be used in your normal sql in the following way.
SELECT * FROM TableName WHERE RegEx(column, 'Hello World') = 1
There is more information about creating and compiling a suitable project here