Convert integer to hex and hex to integer

Question

So, this query works for me (where signal_data is a column) in Sybase, but it does not work in Microsoft SQL Server:

 HEXTOINT(SUBSTRING((INTTOHEX(signal_data)),5,2)) as Signal 

I also use it in Excel (where A1 contains the value):

 =HEX2DEC(LEFT(DEC2HEX(A1),LEN(DEC2HEX(A1))-2)) 

Does anyone know how I will do this in SQL Server?

Answer 1

Convert INT to hex:

 SELECT CONVERT(VARBINARY(8), 16777215) 

Convert hex to INT:

 SELECT CONVERT(INT, 0xFFFFFF) 

Update 2015-03-16

In the above example, there is a limitation that it only works when the HEX value is specified as an integer literal. For completeness, if the value for the conversion is a hexadecimal string (for example, found in the varchar column), use:

 -- If the '0x' marker is present: SELECT CONVERT(INT, CONVERT(VARBINARY, '0x1FFFFF', 1)) -- If the '0x' marker is NOT present: SELECT CONVERT(INT, CONVERT(VARBINARY, '1FFFFF', 2)) 

Note The string must contain an even number of hexadecimal digits. An odd number of digits will result in an error.

More information can be found in the Binary Styles section of CAST and CONVERT (Transact-SQL) . I believe that SQL Server 2008 or later is required.

Answer 2

Actually, the built-in function is called master.dbo.fn_varbintohexstr.

So for example:

 SELECT 100, master.dbo.fn_varbintohexstr(100) 

Gives you

100 0x00000064

Answer 3

SQL Server equivalents for DEC2HEX string in Excel, HEX2DEC functions:

 --Convert INT to hex string: PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), 16777215),2) --DEC2HEX --Convert hex string to INT: PRINT CONVERT(INT,CONVERT(VARBINARY(4),'00FFFFFF',2)) --HEX2DEC 

Answer 4

Convert int to hex:

SELECT FORMAT(512+255,'X')

Answer 5

This is possible with the FORMAT function, available on SQL Server 2012 and later.

 select FORMAT(10,'x2') 

Results in:

 0a 

Answer 6

Here is a SQL server function that converts an integer value to a hexadecimal representation as varchar. Easy to adapt to other types of databases

For example:

 SELECT dbo.ToHex(4095) --> FFF 

SQL:

 CREATE FUNCTION ToHex(@value int) RETURNS varchar(50) AS BEGIN DECLARE @seq char(16) DECLARE @result varchar(50) DECLARE @digit char(1) SET @seq = '0123456789ABCDEF' SET @result = SUBSTRING(@seq, (@value%16)+1, 1) WHILE @value > 0 BEGIN SET @digit = SUBSTRING(@seq, ((@value/16)%16)+1, 1) SET @value = @value/16 IF @value <> 0 SET @result = @digit + @result END RETURN @result END GO 

Answer 7

The traditional 4-bit hex is pretty straight forward. Hex String to Integer (The assumed value is stored in the FHexString field):

 CONVERT(BIGINT,CONVERT(varbinary(4), (SELECT master.dbo.fn_cdc_hexstrtobin( LEFT(FMEID_ESN,8) )) )) 

Hexadecimal integer (Assuming the value is stored in the FInteger field):

 (SELECT master.dbo.fn_varbintohexstr(CONVERT(varbinary,CONVERT(int, FInteger )))) 

It is important to note that when you start using the bit sizes that cause the registry to be exchanged, especially on a computer with Intel, your High and Low and Left and Rights in the registers will change places due to the insignificant nature of Intel. For example, when using varbinary (3) we are talking about the six-character Hex. In this case, your bits are concatenated as the following indexes from right to left "54.32.10". On intel, you expect "76.54.32.22". Since you only use 6 out of 8, you need to remember to do swaps yourself. "76.54" will qualify as your left, and "32.10" will qualify as your right. A comma separates your high and low. Intel changes highs and lows, then left and right. So, to do the conversion ... sigh, you need to change them yourself, for example, the following will convert the first 6 of six characters:

 (SELECT master.dbo.fn_replvarbintoint( CONVERT(varbinary(3),(SELECT master.dbo.fn_cdc_hexstrtobin( --intel processors, registers are switched, so reverse them ----second half RIGHT(FHex8,2)+ --0,1 (0 indexed) LEFT(RIGHT(FHex8,4),2)+ -- 2,3 (oindex) --first half LEFT(RIGHT(FHex8,6),2) --4,5 ))) )) 

This is a bit complicated, so I would try to keep my conversions in a hexadecimal character (varbinary (4)).

So this should answer your question. Vast.

Answer 8

Use master.dbo.fnbintohexstr(16777215) to convert to a varchar view.

Answer 9

 Declare @Dato xml Set @Dato = Convert(xml, '<dato>FF</dato>') Select Cast( rw.value( 'xs:hexBinary( text()[1])' , 'varbinary(max)' ) as int ) From @Dato.nodes('dato') as T(rw) 

Answer 10

Maxim Kozlenko’s answer is good and can be slightly modified to handle encoding a numerical value in any code format. For example:

 CREATE FUNCTION [dbo].[IntToAlpha](@Value int) RETURNS varchar(30) AS BEGIN DECLARE @CodeChars varchar(100) SET @CodeChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' DECLARE @CodeLength int = 26 DECLARE @Result varchar(30) = '' DECLARE @Digit char(1) SET @Result = SUBSTRING(@CodeChars, (@Value % @CodeLength) + 1, 1) WHILE @Value > 0 BEGIN SET @Digit = SUBSTRING(@CodeChars, ((@Value / @CodeLength) % @CodeLength) + 1, 1) SET @Value = @Value / @CodeLength IF @Value <> 0 SET @Result = @Digit + @Result END RETURN @Result END 

Thus, a large number, for example, 150 million, becomes only 6 characters (150,000,000 = "MQGJMU")

You can also use different characters in different sequences as an encryption device. Or pass code characters and character lengths and use as a salting method for encryption.

And vice versa:

 CREATE FUNCTION [dbo].[AlphaToInt](@Value varchar(7)) RETURNS int AS BEGIN DECLARE @CodeChars varchar(100) SET @CodeChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' DECLARE @CodeLength int = 26 DECLARE @Digit char(1) DECLARE @Result int = 0 DECLARE @DigitValue int DECLARE @Index int = 0 DECLARE @Reverse varchar(7) SET @Reverse = REVERSE(@Value) WHILE @Index < LEN(@Value) BEGIN SET @Digit = SUBSTRING(@Reverse, @Index + 1, 1) SET @DigitValue = (CHARINDEX(@Digit, @CodeChars) - 1) * POWER(@CodeLength, @Index) SET @Result = @Result + @DigitValue SET @Index = @Index + 1 END RETURN @Result 

Answer 11

Given:

 declare @hexStr varchar(16), @intVal int 

IntToHexStr:

 select @hexStr = convert(varbinary, @intVal, 1) 

HexStrToInt:

 declare @query varchar(100), @parameters varchar(50) select @query = 'select @result = convert(int,' + @hb + ')', @parameters = '@result int output' exec master.dbo.Sp_executesql @query, @parameters, @intVal output 

Answer 12

 IIF(Fields!HIGHLIGHT_COLOUR.Value="","#FFFFFF","#" & hex(Fields!HIGHLIGHT_COLOUR.Value) & StrDup(6-LEN(hex(Fields!HIGHLIGHT_COLOUR.Value)),"0")) 

Works for me as an expression in font color

Answer 13

To convert hex strings to INT, I have used this in the past. It can be modified to convert any base to INT in fact (Octal, Binary, whatever)

 Declare @Str varchar(200) Set @str = 'F000BE1A' Declare @ndx int Set @ndx = Len(@str) Declare @RunningTotal BigInt Set @RunningTotal = 0 While @ndx > 0 Begin Declare @Exponent BigInt Set @Exponent = Len(@Str) - @ndx Set @RunningTotal = @RunningTotal + Power(16 * 1.0, @Exponent) * Case Substring(@str, @ndx, 1) When '0' then 0 When '1' then 1 When '2' then 2 When '3' then 3 When '4' then 4 When '5' then 5 When '6' then 6 When '7' then 7 When '8' then 8 When '9' then 9 When 'A' then 10 When 'B' then 11 When 'C' then 12 When 'D' then 13 When 'E' then 14 When 'F' then 15 End Set @ndx = @ndx - 1 End Print @RunningTotal 

Answer 14

Below are two functions: dbo.HexToInt and dbo.IntToHex, I use them for such a conversion:

 if OBJECT_ID('dbo.HexToInt') is not null drop function dbo.HexToInt GO create function dbo.HexToInt (@chars varchar(max)) returns int begin declare @char varchar(1), @len int, @i int, @r int, @tmp int, @pow int set @chars = RTRIM(LTRIM(@chars)) set @len = LEN(@chars) set @i = 1 set @r = 0 while @i <= @len begin set @pow = @len - @i set @char = SUBSTRING(@chars, @i, 1) if @char = '0' set @tmp = 0 else if @char = '1' set @tmp = 1 else if @char = '2' set @tmp = 2 else if @char = '3' set @tmp = 3 else if @char = '4' set @tmp = 4 else if @char = '5' set @tmp = 5 else if @char = '6' set @tmp = 6 else if @char = '7' set @tmp = 7 else if @char = '8' set @tmp = 8 else if @char = '9' set @tmp = 9 else if @char = 'A' set @tmp = 10 else if @char = 'B' set @tmp = 11 else if @char = 'C' set @tmp = 12 else if @char = 'D' set @tmp = 13 else if @char = 'E' set @tmp = 14 else if @char = 'F' set @tmp = 15 set @r = @r + @tmp * POWER(16,@pow) set @i = @i + 1 end return @r end 

And the second one:

 if OBJECT_ID('dbo.IntToHex') is not null drop function dbo.IntToHex GO create function dbo.IntToHex (@val int) returns varchar(max) begin declare @r varchar(max), @tmp int, @v1 int, @v2 int, @char varchar(1) set @tmp = @val set @r = '' while 1=1 begin set @v1 = @tmp / 16 set @v2 = @tmp % 16 if @v2 = 0 set @char = '0' else if @v2 = 1 set @char = '1' else if @v2 = 2 set @char = '2' else if @v2 = 3 set @char = '3' else if @v2 = 4 set @char = '4' else if @v2 = 5 set @char = '5' else if @v2 = 6 set @char = '6' else if @v2 = 7 set @char = '7' else if @v2 = 8 set @char = '8' else if @v2 = 9 set @char = '9' else if @v2 = 10 set @char = 'A' else if @v2 = 11 set @char = 'B' else if @v2 = 12 set @char = 'C' else if @v2 = 13 set @char = 'D' else if @v2 = 14 set @char = 'E' else if @v2 = 15 set @char = 'F' set @tmp = @v1 set @r = @char + @r if @tmp = 0 break end return @r end 

Answer 15

You do not have a standard method (i.e. ANSI) for this conversion.

Everything you do will be patented functionality.

I would suggest moving this conversion to your code, and not depending on the database.