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