Search code examples
sql-servervarbinary

Convert decimal to hex string without 0x in SQL Server

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

Solution

  • 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:

    4231