Convert INT values to HEX in SQL Server

Question

Alright I am not experienced in SQL Server but anybody here knows how can I transform for example this set of four INT values into this value in HEX?

I have for example:

And I need it converted to:

0x03050303

So for example if I have:

3533 then I get 0x03050303
3459 then I get 0x03040509

Thanks in advance!

Please note that this is not just a hex conversion, this is something else, like separating each digit out into its own byte. Hex for 3533 would be 0xDCD — Lasse V. Karlsen, Feb 02 '15 at 10:07
did you try this http://stackoverflow.com/questions/703019/convert-integer-to-hex-and-hex-to-integer ? — Sachin, Feb 02 '15 at 10:08

score 0 · Accepted Answer · answered Feb 02 '15 at 10:12

Since this is not normal hex conversion, normal tricks won't apply.

The hexadecimal value for the decimal value 3533 is 0xDCD and that's not what you want.

Instead you want each digit of the decimal value separated out into its own byte, so basically you want this:

DEC   = abcd
"HEX" = 0a0b0c0d

You can do this with this simple calculation:

DECLARE @VALUE INT = 1234
SELECT CONVERT(VARBINARY(8),
    (@VALUE % 10) +
    (@VALUE / 10 % 10) * 16*16 +
    (@VALUE / 100 % 10) * 16*16*16*16 +
    (@VALUE / 1000 % 10) * 16*16*16*16*16*16
    ) AS X

Output:

01020304

Note that the integer value can be found without the conversion, the conversion above is just to get it formatted like a hex value when testing. "Hex" is just a visual representation, the underlying number is the same.

In other words, the numeric value that corresponds to 0x0a0b0c0d can be found by just:

DECLARE @VALUE INT = 1234
SELECT
    ((@VALUE % 10) +
     (@VALUE / 10 % 10) * 16*16 +
     (@VALUE / 100 % 10) * 16*16*16*16 +
     (@VALUE / 1000 % 10) * 16*16*16*16*16*16) AS X

The hex output is just a formatted representation.

Hej Lasse, tak for hjælpen ;) Yes I think adding a weighting value is the way to go! — Metalex, Feb 02 '15 at 10:14

score 0 · Answer 2 · answered Feb 02 '15 at 10:12

Use this:

declare @i as int,@str as varchar(20),@counter as int;
set @counter = 1;
set @i = 3459 ;
set @str = '0x'
while (@counter <= len(@i))
begin
set @str = @str + '0' + substring(convert(varchar(20),@i),@counter,1)
set @counter = @counter +1
end
print @str

Convert INT values to HEX in SQL Server

2 Answers2