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

Leave a Reply

Avatar placeholder

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