SQL Server CLR in SQL Server 2008

Eg. Remove HTML tags for the string.


First, make sure that the CLR integration is enabled. This can be accomplished by using the server facets in SQL Server 2008, the Surface Area Configuration tool 

sp_configure 'clr enabled', 1  
GO  
RECONFIGURE  
GO

Next, follow these steps:

• Open Visual Studio 2010
• Click on "New Project"
• Choose the Database ---> SQL Server ---> Visual C# SQL CLR Database Project template.
• Make sure that the project targets .NET 2 / .NET 3 / .NET 3.5.
• Set up a connection to your database, test the connection and click OK
• Right click on the project and add a user defined function as explained in the next section

Creating the user defined function in the SQL Server CLR

 [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString RemoveHTMLTags(SqlString s)

    {

        if (s.IsNull) return String.Empty;

        string s1 = s.ToString().Trim();

        if (s1.Length == 0) return String.Empty;

        string pattern = @"<[^>]*?>|<[^>]*>";

        Regex rgx = new Regex(pattern);

        return rgx.Replace(s1, String.Empty);

    }

Create Assembly and Function

After the code has been compiled you need to create the assembly and the function with SQL Server.  To do this, run these commands in the database where you want to use the function. 

The assembly ties an internal object to the external DLL that was created and the function is similar to a normal SQL Server function.

For the function you will see three components that are referenced CLRFunctions.CLRFunctions.SortString.

• CLRFunctions - the assembly reference.
• CLRFunctions - the class reference in the C# code.
• SortString - the function reference in the C# code.

CREATE ASSEMBLY CLRFunctions FROM 'C:\

RemoveHTMLTags

.dll'  
GO
CREATE FUNCTION dbo.

RemoveHTMLTags

(   
 @name AS NVARCHAR(255)   
)     RETURNS NVARCHAR(255)    AS EXTERNAL NAME 

RemoveHTMLTags

.

UserDefinedFunctions

.

RemoveHTMLTags

GO


To Test the Data

SELECT dbo.RemoveHTMLTags('Hello World')