Convert hexadecimal to 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.

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)

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:

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: