SQL Tips by Namwar Rizvi

January 20, 2009

TSQL Function to encode HTML Text

Filed under: Uncategorized — namwar @ 9:36 PM
Tags: , , , ,

While browsing through the Sharepoint content database, I found a very useful TSQL utility function which can be used in any application. This function encodes the given html so that it can be safely used in XML tags and other html rendering requirements.

Please note,  this function is originally provided in Sharepoint content database and I have just copied it because of its usefulness. PLEASE DO NOT CALL it directly from Sharepoint database as it is not recommended and supported by Microsoft. Just create this function in your own database and modify it as per your requirement.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
 go

CREATE FUNCTION [dbo].[fn_HtmlEncode](
     @Value nvarchar(1023),
     @PreserveNewLine bit)
 RETURNS nvarchar(4000)
AS
BEGIN
     DECLARE @Result nvarchar(4000)
     SELECT @Result = @Value
     IF @Result IS NOT NULL AND LEN(@Result) > 0
     BEGIN
         SELECT @Result = REPLACE(@ResultN'&'N'&')
         SELECT @Result = REPLACE(@ResultN'<'N'&lt;')
         SELECT @Result = REPLACE(@ResultN'>'N'&gt;')
         SELECT @Result = REPLACE(@ResultN''''N''')
         SELECT @Result = REPLACE(@ResultN'"'N'&quot;')
         IF @PreserveNewLine = 1
             SELECT @Result = REPLACE(@ResultCHAR(10), CHAR(10) + N'<br>')
     END
     RETURN @Result
END

Advertisements

1 Comment »

  1. Good post! Just what I was looking for.

    Comment by Sunil — August 26, 2009 @ 12:40 AM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: