Convert integer to hexa in sql

▲
-1
▼

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?

sql-server
asked on Stack Overflow Nov 22, 2018 by Pinky • edited Nov 22, 2018 by Birel

3 Answers

▲
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

answered on Stack Overflow Nov 22, 2018 by Diado • edited Nov 22, 2018 by Diado
▲
0
▼

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'.

answered on Stack Overflow Nov 22, 2018 by Larnu
▲
0
▼

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.

answered on Stack Overflow Nov 22, 2018 by Jodrell

User contributions licensed under CC BY-SA 3.0