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)

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

--testing

SELECT dbo.fn_dectobase(16776976, 16);

Output: FFFF10

SELECT dbo.fn_dectobase(16776976, 2);

Output: 111111111111111100010000

Page updated

Google Sites

Report abuse