Conversion of decimals to hexadecimals in SQL

While trying to convert some integers to hex values in SQL I found a few options that worked on ints and bigints but nothing that worked on anything larger and as I was needing to convert some integers of datatype decimal(20) I created the below function which is based on that found here.

The only real change other than the input datatype was adding a floor on the division of the value by the base to remove the decimal places added by the decimal datatype.


CREATE FUNCTION ConvertToBase
(  
    @value AS decimal(20),
    @base AS INT
) RETURNS VARCHAR(MAX) AS BEGIN

    -- some variables
    DECLARE @characters CHAR(36),
            @result VARCHAR(MAX);

    -- the encoding string and the default result
    SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
           @result = '';

    -- make sure it's something we can encode.  you can't have
    -- base 1, but if we extended the length of our @character
    -- string, we could have greater than base 36
    IF @value < 0 OR @base < 2 OR @base > 36 RETURN NULL;

    -- until the value is completely converted, get the modulus
    -- of the value and prepend it to the result string.  then
    -- devide the value by the base and truncate the remainder
    WHILE @value > 0
        SELECT @result = SUBSTRING(@characters, @value % @base + 1, 1) + @result,
               @value = floor(@value / @base);

    -- return our results
    RETURN @result;

END
go

select dbo.ConvertToBase(18446744073709551615, 16)
--ffffffffffffffff

Leave a Reply

Your email address will not be published. Required fields are marked *