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

This post was written by namwar on January 12, 2009
Posted Under: How-To, SQL Server 2005, SQL Server 2008, TSQL, Utility Functions

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

    /* Return the final answer */
    return @answer;
End

Reader Comments

very useful, thankyou very much

#1 
Written By daniel on August 9th, 2011 @ 5:13 pm

Add a Comment

required, use real name
required, will not be published
optional, your blog address