Convert decimal to hex string without 0x in SQL Server

Question

I want to convert

string
------
BB
C1
GB

to

hex
---
4242
4331
4742

using

SELECT CONVERT(BINARY(2), 'B1')

Result is '0x4231'

but I want remove the 0x from the result, so I tried varbinary to string:

SELECT CONVERT([VARCHAR](MAX), CONVERT(BINARY(2), 'B1', 2))

result is '?'

Then I tried

SELECT SUBSTRING(CONVERT(BINARY(2), 'B1'), 2, 4)

result is '0x42'

How to convert 'B1' to '4231'?

DO NOT confuse the actual binary value stored in a variable/column with the respresentation used to display it in a manner that you can SEE. The "0x" bit (in your first query) is an artifact of the manner in which SSMS displays that particular datatype. — SMor
– SMor, Commented Nov 13, 2019 at 1:40
Rarely is this something you need to do using tsql. So that begs the question of what are you trying to accomplish. — SMor
– SMor, Commented Nov 13, 2019 at 1:42

John Wu · Accepted Answer · 2019-11-13 02:51:01Z

1

Convert to hex using the system function master.dbo.fn_varbintohexstr, then remove the first two characters.

SELECT SUBSTRING(master.dbo.fn_varbintohexstr(convert(binary(2), 'B1')),3,999)

Output:

answered Nov 13, 2019 at 2:51

John Wu

52.5k8 gold badges50 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

toco · Accepted Answer · 2019-11-15 05:12:33Z

0

solved it myself

SELECT convert(varchar(4), convert(binary(2), ('B1')), 2)

answered Nov 15, 2019 at 5:12

toco

171 silver badge5 bronze badges