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