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

January 12, 2009

TSQL Function to convert decimal to Hex, Octal or any other base

Frequently I see the questions in newsgroups about a function to convert integer value to other bases like base 2 (binary), base 8 (octal) and base 16(hex). Following TSQL function, which was orginally mentioned by Itzik Ben-Gan in his book Inside Microsoft SQL Server 2005:TSQL Querying, provides you the ability to convert a given integer into any target base. I have just updated the function with more meaningful names and added some comments to clear the logic.

Here it is:

USE [AdventureWorks]
GO;

IF EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fn_decToBase]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[fn_decToBase]
Go
Create function [dbo].[fn_decToBase]
(
@val as BigInt,
@base as int
)
returns varchar(63)
as
Begin
/* Check if we get the valid base */
If (@val<0) OR (@base < 2) OR (@base> 36) Return Null;

/* variable to hold final answer */
Declare @answer as varchar(63);

/* Following variable contains all
possible alpha numeric letters for any base
*/
Declare @alldigits as varchar(36);
Set @alldigits='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

/* Set the initial value of
final answer as empty string
*/
Set @answer='';

/* Loop until your source value is greater than 0 */
While @val>0
Begin
Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
Set @val = @val / @base;
End

</code>

<code>/* Return the final answer */
return @answer;
End

Create a free website or blog at WordPress.com.