Convert integer to hexa in sql

Question

I want to convert int to hexadecimal in SQL.

Example:

SELECT CONVERT(VARBINARY(8), 162)

Result :0x000000A2

Actual value is this

A2

Why I am getting unnecessary part at prefix?

Can I remove previous part?

What is right way to handle it?

What unnecessary part? That value looks correct for a `varbinary` to me — Thom A, Nov 22 '18 at 10:41
Possible duplicate of [Convert integer to hex and hex to integer](https://stackoverflow.com/questions/703019/convert-integer-to-hex-and-hex-to-integer) — AlexK, Nov 22 '18 at 10:50

Diado · Answer 1 · 2018-11-22T11:22:16.817

2

To quote the documentation:

When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.

You're specifying VARBINARY(8) in your query, so the result is padded with zeros to that length. If you need the value without the padding for some reason, specify VARBINARY(1), which will return 0xA2.

Note: They're both the same value

Alternatively, if you just want a 2 character string:

SELECT RIGHT(CONVERT(VARCHAR(8),CONVERT(VARBINARY(8),162),2), 2)

Which will return A2

edited Nov 22 '18 at 11:22

answered Nov 22 '18 at 10:43

Diado

2,229
3
18
21

It work for me.PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(1), 162),2) – Pinky Nov 22 '18 at 11:27
1

@Pinky, okay, as long as you know the length of the significant figures – Jodrell Nov 22 '18 at 11:36

score 0 · Answer 2 · answered Nov 22 '18 at 10:50

At a complete a total guess in the absence of any response from the OP:

SELECT V.BloatedHex,
       ISNULL(STUFF(V.BloatedHex,1,NULLIF(PATINDEX('%[^0]%',V.BloatedHex),0)-1,''),0)
FROM (VALUES(STUFF(CONVERT(varchar(10),CONVERT(varbinary(8),162),1),1,2,''))) V(BloatedHex);

This returns the varchar(10) value 'A2'.

score 0 · Answer 3 · answered Nov 22 '18 at 11:30

You can do it in one statement like this,

DECLARE @someNumber BIGINT = 162;

WITH Hex AS (
SELECT CONVERT(VARCHAR(34), CONVERT(VARBINARY(8), @someNumber), 2) [Value]
)
SELECT SUBSTRING([Value], PATINDEX('%[^0]%', [Value]), 34) FROM Hex

;

This does not use any unsupported internal functions and attempts to minimize string manipulation.

Better still, don't write this kind of presentation code with TSQL, it is not what it is good at. Worry about making it look pretty when you display the value to the user.

Convert integer to hexa in sql

3 Answers3