Function for converting int (integer) to hex (hexadecimal) string in SQL Server

Integer to hexadecimal in SQL Server

Although such as requirement should be relatively uncommon, as this purely a format concern, and numbers should not be stored as strings, sometimes it can necessary to convert an integer to a hex string in SQL server. If you're looking for the opposite, please see hex to integer in SQL Server. There are other ways of doing this, but the function below offers the convenience of accepting the integer as the parameter and returning a string consisting of hexadecimal characters as the output.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[IntToHex]
(
	@Value bigint,
	@TrimLeftZeroes bit = 1
)
RETURNS varchar(16)
AS
BEGIN
	declare @Output as varchar(16)

	declare @HexDigits smallint,
	@Digits tinyint,
	@Base bigint,
	@Factor bigint,
	@Quotient int,
	@HexChar char,
	@Exp int

	set @Output = ''
	set @Base=16	

	set @Digits = len(@Value)
	set @HexDigits = @Digits - @Digits/6 

	if @HexDigits > 16
	begin
		--overflow		
		return null
	end

	set @Exp = @HexDigits -1

	while @Exp >= 0
	begin
		set @Factor = power(@Base,@Exp)
		set @Quotient = @Value / @Factor
		set @Value = @Value - @Quotient*@Factor
		if @Quotient  0 or len(@Output)>0
		begin			
			set @Output = @Output + @HexChar			
		end
		set @Exp = @Exp - 1
	end	

	return @Output
END






Information Error Confirmation required