Qfyilyi

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?

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

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

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.