4

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?

8

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.

| improve this answer | |
2

This returns the value you are looking for. I would strongly suggest if you have any control over this process that you stop storing data in this format.

DECLARE @hexstr nvarchar(40) = '0x' + SUBSTRING(CONVERT(NVARCHAR(100), 0x008B010000000089FF32323633393933352D4B434E000000, 1),21, 24);
declare @ind int, @byte1 int, @byte2 int, @binvalue varbinary(20)

set @binvalue = 0x

if lower(substring(@hexstr, 1, 2)) = '0x'
    set @ind = 3
else
    set @ind = 1

while ( @ind <= len(@hexstr) )
begin            
    set @byte1 = ascii(substring(@hexstr, @ind, 1))
    set @byte2 = ascii(substring(@hexstr, @ind + 1, 1))
    set @binvalue = @binvalue + convert(binary(1), 
              case 
                    when @byte1 between 48 and 57 then @byte1 - 48  
                    when @byte1 between 65 and 70 then @byte1 - 55  
                    when @byte1 between 97 and 102 then @byte1 - 87 
                    else null end * 16 +
              case 
                    when @byte2 between 48 and 57 then @byte2 - 48  
                    when @byte2 between 65 and 70 then @byte2 - 55  
                    when @byte2 between 97 and 122 then @byte2 - 87 
                    else null end) 
    set @ind = @ind + 2 
end 

SELECT CONVERT(VARCHAR(50), @binvalue)

enter image description here

In order to wrap this into a function that returns a table (a so-called Table-Valued-Function), you could do something like:

IF COALESCE(OBJECT_ID('dbo.GetProductCodeFromVARBINARY'), 0) <> 0
BEGIN
    DROP FUNCTION dbo.GetProductCodeFromVARBINARY;
END
GO
CREATE FUNCTION GetProductCodeFromVARBINARY
(
    @Bin VARBINARY(64)
)
RETURNS @VarResults TABLE
(
    ProductCode VARCHAR(50) NULL
)
AS
BEGIN
    DECLARE @hexstr nvarchar(40) = '0x' + SUBSTRING(CONVERT(NVARCHAR(100), @Bin, 1),21, 24);
    declare @ind int, @byte1 int, @byte2 int, @binvalue varbinary(20)

    set @binvalue = 0x

    if lower(substring(@hexstr, 1, 2)) = '0x'
        set @ind = 3
    else
        set @ind = 1

    while ( @ind <= len(@hexstr) )
    begin            
        set @byte1 = ascii(substring(@hexstr, @ind, 1))
        set @byte2 = ascii(substring(@hexstr, @ind + 1, 1))
        set @binvalue = @binvalue + convert(binary(1), 
                  case 
                        when @byte1 between 48 and 57 then @byte1 - 48  
                        when @byte1 between 65 and 70 then @byte1 - 55  
                        when @byte1 between 97 and 102 then @byte1 - 87 
                        else null end * 16 +
                  case 
                        when @byte2 between 48 and 57 then @byte2 - 48  
                        when @byte2 between 65 and 70 then @byte2 - 55  
                        when @byte2 between 97 and 122 then @byte2 - 87 
                        else null end) 
        set @ind = @ind + 2 
    end 

    INSERT INTO @VarResults (ProductCode)
    VALUES (CONVERT(VARCHAR(50), @binvalue));

    RETURN;
END

To run this, you can either do:

SELECT *
FROM dbo.GetProductCodeFromVARBINARY(0x008B010000000089FF32323633393933352D4B434E000000);

which returns this:

enter image description here

Or, if you need to JOIN this to another table containing the VARBINARY values, you could do this:

CREATE TABLE dbo.VarBinValues
(
    ProdCode VARBINARY(64) NULL
);

INSERT INTO dbo.VarBinValues (ProdCode)
VALUES (0x008B010000000089FF32323633393933352D4B434E000000);

SELECT vbv.ProdCode
    , pc.ProductCode
FROM dbo.VarBinValues vbv
CROSS APPLY dbo.GetProductCodeFromVARBINARY(vbv.ProdCode) pc

Results:

enter image description here

| improve this answer | |
0

I had a similar problem (see https://stackoverflow.com/questions/63043157/converting-binary-data-to-ascii-in-sql-server-with-illegal-characters).

I found a solution for my purposes, which uses the heuristic approach of converting the binary to a hex string, then doing string replacement of "00", before doing back to binary and string again.

IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL drop table #temp
select 0x24000000008B010000000089FF32323633393933352D4B434E000000 col into #temp

select convert(varchar(max),convert(varbinary(max),
  REPLACE(
    convert(varchar(max),col, 1)
    ,'00',''
  )
,1),0) 
from
    #temp
where
    col like '%KCN%'

This returns $‹‰ÿ22639935-KCN

(This depends on the heuristic of not caring about converting e.g. 0x50 0x03 to 0x53 and similar. For my purposes, this didn't matter, as in ASCII, 0x0z, where z is 1 to f, represents control characters which didn't occur around the text I was interested in).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.