Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - Convert Decimal to Base n

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        9 Aug 2005

Description

A very brute-force but effective and accurate way of converting any decimal number to a string representation in Base n (where n is a number from 10 to 36)
This was developed when SQL 2000 was predominant and SQL 2005 CTEs hadn't been widely adopted, and I'm sure there is a more efficient way of doing this with a CTE.  One day I will revisit it, but I've found that the need for it to be very infrequent and usually in a batch job rather than inline SQL, so performance hasn't generally been an issue
 
Note:  There is no bounds checking of the @Base parameter, so unpredictable results will occur if a @Base of greater than 36 is supplied.

Code

CREATE FUNCTION dbo.fn_dectobase(@val AS bigint, @base AS int)
RETURNS varchar(63)
AS
BEGIN
DECLARE
  
@r AS varchar(63),
  
@alldigits AS varchar(36);

SET @alldigits = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';

SET @r = '';

WHILE @val > 0
BEGIN
   SET
@r = SUBSTRING(@alldigits, @val % @base + 1, 1) + @r;
  
SET @val = @val / @base;
END

RETURN
@r;
END
GO

/*
--testing
SELECT dbo.fn_dectobase(16776976, 16);
Output: FFFF10

SELECT dbo.fn_dectobase(16776976, 2);
Output: 111111111111111100010000

*/



ċ
Convert from Decimal to Base n.sql
(1k)
Andy Hughes,
Jun 23, 2012, 1:12 AM
Comments