SQL Tips by Namwar Rizvi

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]

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]
Create function [dbo].[fn_decToBase]
@val as BigInt,
@base as int
returns varchar(63)
/* 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 the initial value of
final answer as empty string
Set @answer='';

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


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

Create a free website or blog at WordPress.com.