Convert hexadecimal to varchar

I have a column with values saved as hexadecimal.

When I run this query:

select column from  table
where column like '%22639935-KCN%';

This is the output I get:

0x24000000008B010000000089FF32323633393933352D4B434E000000

I would like to turn the output into a varchar. This should give me a value resembling what is in the like statement.

My attempts so far have been:

SELECT CONVERT(VARCHAR(MAX), 
       0x24000000008B010000000089FF32323633393933352D4B434E000000)

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), 
       '0x24000000008B010000000089FF32323633393933352D4B434E000000', 2))

However, I get this outcome:

$

How can I convert this hexadecimal value into a varchar?

It looks like your varbinary string got injected with some nonsense. All of the sequences of two 00 values are null characters, so this is why your string is terminating upon conversion. The first character is 0x24 (dollar sign), which is why the output is just a dollar sign.

SELECT CONVERT(varchar(60), 0x2400....anything....);
-- is equivalent to:
SELECT CONVERT(varchar(60), 0x24);

Now, if I take your string and strip out all of the 00 sequences:

SELECT CONVERT(VARCHAR(60), 0x248B0189FF32323633393933352D4B434E);

I get something close; again, there is a bunch of garbage in there, but the string you're after is in there:

$‹‰ÿ22639935-KCN

You can ignore the leading garbage by just taking the original value and performing RIGHT() against it, but this assumes that the important part of the string is always the same length (we can't tell you if that's true).

SELECT CONVERT(VARCHAR(60),     
       RIGHT(0x24000000008B010000000089FF32323633393933352D4B434E000000, 15));

Or using SUBSTRING, but this assumes that the garbage at the beginning of the string is always the same length:

SELECT CONVERT(VARCHAR(60), 
       SUBSTRING(0x24000000008B010000000089FF32323633393933352D4B434E000000, 14, 46));

We also can't possibly tell you why that garbage is there and if it has any additional meaning. You'll need to find out how the values got encoded this way in the first place. The value you wanted encoded, 22639935-KCN, should have looked a little differently as varbinary:

SELECT CONVERT(VARBINARY(32), '22639935-KCN');

--------------------------
0x32323633393933352D4B434E

So, again, you will need to do some research to find out why this value wasn't encoded this way. We can't answer all of this because we didn't design your system or store those values.

StackExchange Database Administrators Q#132996, answer score: 8