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