I want to convert
hexadecimal in SQL.
SELECT CONVERT(VARBINARY(8), 162)
Actual value is this
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.
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
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
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
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.
User contributions licensed under CC BY-SA 3.0