Encode html in sql server

It's a bit late, but anyway, here the proper ways:

HTML-Encode (HTML encoding = XML encoding):

DECLARE @s NVARCHAR(100)
SET @s = 'unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"'
SELECT (SELECT @s FOR XML PATH(''))

HTML-encode in a query:

SELECT 
    FIELD_NAME  
    ,(SELECT FIELD_NAME AS [text()] FOR XML PATH('')) AS FIELD_NAME_HtmlENcoded 
FROM TABLE_NAME

HTML-Decode:

SELECT CAST('' + '<root>Test&123' + '' AS XML).value(N'(root)[1]', N'varchar(max)');

If you want to do it properly, you can use a CLR-stored procedure.
However, it gets a bit complicated, because you can't use the System.Web-Assembly in CLR-stored-procedures (so you can't do System.Web.HttpUtility.HtmlDecode(htmlEncodedStr);). So you have to write your own HttpUtility class, which I wouldn't recommend, especially for decoding.

Fortunately, you can rip System.Web.HttpUtility out of the mono sourcecode (.NET for Linux). Then you can use HttpUtility without referencing system.web.

Then you write this CLR-Stored-Procedure:

using System;
using System.Collections.Generic;
using System.Text;

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
//using Microsoft.SqlServer.Types;


namespace ClrFunctionsLibrary
{


    public class Test
    {


        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString HtmlEncode(SqlString sqlstrTextThatNeedsEncoding)
        {
            string strHtmlEncoded = System.Web.HttpUtility.HtmlEncode(sqlstrTextThatNeedsEncoding.Value);
            SqlString sqlstrReturnValue = new SqlString(strHtmlEncoded);

            return sqlstrReturnValue;
        }


        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString HtmlDecode(SqlString sqlstrHtmlEncodedText)
        {
            string strHtmlDecoded = System.Web.HttpUtility.HtmlDecode(sqlstrHtmlEncodedText.Value);
            SqlString sqlstrReturnValue = new SqlString(strHtmlDecoded);

            return sqlstrReturnValue;
        }


        // ClrFunctionsLibrary.Test.GetPassword
        //[Microsoft.SqlServer.Server.SqlFunction]
        //public static SqlString GetPassword(SqlString sqlstrEncryptedPassword)
        //{
        //    string strDecryptedPassword = libPortalSecurity.AperturePortal.DecryptPassword(sqlstrEncryptedPassword.Value);
        //    SqlString sqlstrReturnValue = new SqlString(sqlstrEncryptedPassword.Value + "hello");

        //    return sqlstrReturnValue;
        //}

        public const double SALES_TAX = .086;

        // http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx
        [SqlFunction()]
        public static SqlDouble addTax(SqlDouble originalAmount)
        {
            SqlDouble taxAmount = originalAmount * SALES_TAX;

            return originalAmount + taxAmount;
        }


    } // End Class Test


} // End Namespace ClrFunctionsLibrary

And register it:

GO

/*
--http://stackoverflow.com/questions/72281/error-running-clr-stored-proc
-- For unsafe permission
EXEC sp_changedbowner 'sa'
ALTER DATABASE YOUR_DB_NAME SET TRUSTWORTHY ON 

GO
*/


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HtmlEncode]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[HtmlEncode]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HtmlDecode]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[HtmlDecode]
GO




IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'ClrFunctionsLibrary' and is_user_defined = 1)
DROP ASSEMBLY [ClrFunctionsLibrary]

GO


--http://msdn.microsoft.com/en-us/library/ms345101.aspx



CREATE ASSEMBLY [ClrFunctionsLibrary]
AUTHORIZATION [dbo]
FROM 'D:\username\documents\visual studio 2010\Projects\ClrFunctionsLibrary\ClrFunctionsLibrary\bin\Debug\ClrFunctionsLibrary.dll' 
WITH PERMISSION_SET = UNSAFE  --EXTERNAL_ACCESS  --SAFE
;

GO




CREATE FUNCTION [dbo].[HtmlDecode](@value [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
-- [AssemblyName].[Namespace.Class].[FunctionName]
EXTERNAL NAME [ClrFunctionsLibrary].[ClrFunctionsLibrary.Test].[HtmlDecode]
GO





CREATE FUNCTION [dbo].[HtmlEncode](@value [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
-- [AssemblyName].[Namespace.Class].[FunctionName]
EXTERNAL NAME [ClrFunctionsLibrary].[ClrFunctionsLibrary.Test].[HtmlEncode]
GO



/*
EXEC sp_CONFIGURE 'show advanced options' , '1';
 GO
 RECONFIGURE;
 GO
 EXEC sp_CONFIGURE 'clr enabled' , '1'
 GO
 RECONFIGURE;
 GO

EXEC sp_CONFIGURE 'show advanced options' , '0';
 GO
 RECONFIGURE;
*/

Afterwards, you can use it like normal functions:

SELECT
     dbo.HtmlEncode('helloäÖühello123') AS Encoded
    ,dbo.HtmlDecode('helloäÖühello123') AS Decoded 

Anybody who just copy-pastes, please note that for efficiency reasons, you would use

public const double SALES_TAX = 1.086;

// http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx
[SqlFunction()]
public static SqlDouble addTax(SqlDouble originalAmount)
{
     return originalAmount * SALES_TAX;
}

if you'd use this function in production.

See here for the edited mono classes:
http://pastebin.com/pXi57iZ3
http://pastebin.com/2bfGKBte

You need to define NET_2_0 in the build options