sql server - SQL convert number to string representation of any base (binary, hexadecimal, ..., tricontahexadecimal)

Question

How to convert a number to it string representation for a desired numeric base using SQL, for example convert 45 to the base 2(binary), 8(octantal),16(hexadecimal), ..36.

The requirement is to use the numbers [0-9] and the uppercase characters [A-Z], the total of available characters is 36.

I need to convert for example 45 to base 36, the output must be "19", or use any range base form 2 to 36.

Answer 1

This is the solution made to convert a number to the string representation to any numeric base. The solution is a function that run on SQL Server, it receives the base and number parameter. The first one parameter is the base number that you want to get and the second one parameter is the number that you want to convert. The algorithm used was taken from the site mathbits.com .

Using the same example from the site of the algorithm, if you want to convert 5 base 10 into base 2.

The process is:

1. Divide the "desired" base (in this case base 2) INTO the number you are trying to convert.
2. Write the quotient (the answer) with a remainder like you did in elementary school.
3. Repeat this division process using the whole number from the previous quotient (the number in front of the remainder).
4. Continue repeating this division until the number in front of the remainder is only zero.
5. The answer is the remainders read from the bottom up.

You can see the algorithm and more examples here.

A function in SQL is the best choice to make them useful globally in the SQL Server Instance, the code to do the conversion is the following:

IF OBJECT_ID (N'dbo.NUMBER_TO_STR_BASE', N'FN') IS NOT NULL
    DROP FUNCTION dbo.NUMBER_TO_STR_BASE;
GO
CREATE FUNCTION dbo.NUMBER_TO_STR_BASE (@base int,@number int)
RETURNS varchar(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @dividend int = @number
        ,@remainder int = 0 
        ,@numberString varchar(MAX) = CASE WHEN @number = 0 THEN '0' ELSE '' END ;
     SET @base = CASE WHEN @base <= 36 THEN @base ELSE 36 END;--The max base is 36, includes the range of [0-9A-Z]
     WHILE (@dividend > 0 OR @remainder > 0)
         BEGIN
            SET @remainder = @dividend % @base ; --The reminder by the division number in base
            SET @dividend = @dividend / @base ; -- The integer part of the division, becomes the new divident for the next loop
            IF(@dividend > 0 OR @remainder > 0)--check that not correspond the last loop when quotient and reminder is 0
                SET @numberString =  CHAR( (CASE WHEN @remainder <= 9 THEN ASCII('0') ELSE ASCII('A')-10 END) + @remainder ) + @numberString;
     END;
     RETURN(@numberString);
END
GO

After you execute the above code, you can test them calling the function in any query or even in a complex TSL code.

SELECT dbo.NUMBER_TO_STR_BASE(16,45) AS 'hexadecimal';
-- 45 in base 16(hexadecimal) is 2D 
SELECT dbo.NUMBER_TO_STR_BASE(2,45) AS 'binary';
-- 45 in base 2(binary) is 101101
SELECT dbo.NUMBER_TO_STR_BASE(36,45) AS 'tricontahexadecimal';
-- 45 in base (tricontaexadecimal) is 19
SELECT dbo.NUMBER_TO_STR_BASE(37,45) AS 'tricontahexadecimal-test-max-base';
--The output will be 19, because the maximum base is 36,
-- which correspond to the characters [0-9A-Z]

Feel free to comment or suggest improvements, i hope it to be useful