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]
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
Advertisements

4 Comments »

  1. 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 | Reply

  2. Great!

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

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

  3. 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 | Reply

  4. 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: