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
0 Comments