Enabling MSSQL CLR Functions

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
GO

 

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
	FROM 'C:\Temp\MyExtenstion.dll'

 

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
GO

 

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

E-mail Kick it! DZone it! del.icio.us Permalink


Pingbacks and trackbacks (1)+