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

### Like this:

Like Loading...

*Related*

Great function, exactly what I needed. Google comes through again! I liked this so much I reposted it at my own blog at http://geekswithblogs.net/bbiales/archive/2009/05/04/131732.aspx. I fixed a few typos…

– if you cut and paste your code the single quotes are all directional, just need a couple quick Replace All’s to fix

– in the SELECT to find the function if it already exists and delete it – the FROM clause references sys.objects instead of simply sysobjects.

– Also in the SELECT to find the function if it already exists and delete it – the WHERE clause references column OBJECT_ID, but it should be just ID.

I’m still using a SQL Server 2000 database, so maybe that’s why I had to make minor changes… Not sure. But other than these minor issues, the algorithm rocks, thanks for making it easy to find.

Comment by Brian Biales — May 4, 2009 @ 10:39 PM |

Great!

Do you happen to have a decode function? i.e. fn_BaseToDec?

Comment by Lars — August 6, 2009 @ 6:47 PM |

To convert base 36 to dec please use

CREATE FUNCTION [dbo].[fn_baseToDec] (@val AS VARCHAR(63), @base AS INT) RETURNS BIGINT

AS

BEGIN

IF (@base 36) RETURN NULL;

/* variable to hold final answer */

DECLARE @answer AS BIGINT;

DECLARE @pos AS INT;

DECLARE @i AS INT;

/* Following variable contains all

possible alpha numeric letters for any valid base

*/

DECLARE @alldigits AS VARCHAR(36);

SET @alldigits=’0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’

/* Set the initial value of

final answer as empty string

*/

SET @answer=0;

SET @pos=LEN(@val) – 1;

SET @i = 1;

/* Loop while the source value remains greater than 0 */

WHILE @i <= LEN(@val)

BEGIN

SET @answer = @answer + CAST((CHARINDEX(SUBSTRING(@val, @i, 1), @alldigits)-1)*POWER(CAST(@base AS BIGINT), @pos) AS BIGINT);

SET @pos = @pos – 1;

SET @i = @i + 1;

END

/* Return the final answer */

RETURN @answer;

END

Comment by Artit P. — March 29, 2010 @ 6:34 PM |

Oh, sorry. Some of my code is missing when display as HTML. The correct one is

CREATE FUNCTION [dbo].[fn_baseToDec] (@val AS VARCHAR(63), @base AS INT) RETURNS BIGINT

AS

BEGIN

IF (@base < 2) OR (@base > 36) RETURN NULL;

/* variable to hold final answer */

DECLARE @answer AS BIGINT;

DECLARE @pos AS INT;

DECLARE @i AS INT;

/* Following variable contains all

possible alpha numeric letters for any valid base

*/

DECLARE @alldigits AS VARCHAR(36);

SET @alldigits=’0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’

/* Set the initial value of

final answer as empty string

*/

SET @answer=0;

SET @pos=LEN(@val) – 1;

SET @i = 1;

/* Loop while the source value remains greater than 0 */

WHILE @i <= LEN(@val)

BEGIN

SET @answer = @answer + CAST((CHARINDEX(SUBSTRING(@val, @i, 1), @alldigits)-1)*POWER(CAST(@base AS BIGINT), @pos) AS BIGINT);

SET @pos = @pos – 1;

SET @i = @i + 1;

END

/* Return the final answer */

RETURN @answer;

END

Comment by Artit P. — March 29, 2010 @ 6:37 PM |