Sql-server – Right Trimming Binary Data in SQL Server

sql serversql-server-2008-r2

Scenario:

I am inserting a string into a binary field (CONTEXT_INFO) and then later attempting to pull it out and convert it back to a string. When I do, the resulting string has a length of 128 because it has trailing null characters.

Example:

DECLARE @string VARCHAR(128)
DECLARE @binary VARBINARY(128)

SET @string = 'abcdefg'
SET @binary = CONVERT(VARBINARY(128), @string) --0x61626364656667000000...
SET CONTEXT_INFO @binary
SET @binary = CONTEXT_INFO()

-- I would like to change the following line so it trims trailing null chars
SET @string = CONVERT(VARCHAR(128), @binary)

SELECT
    @binary AS [binary],
    DATALENGTH(@binary) AS [binary.Length], --128 as expected
    @string AS [string],
    DATALENGTH(@string) AS [string.Length] --This is 128, but I need it to be 7

Question:

How can I trim the trailing null characters when I convert the binary field to a string?

Best Answer

This looks kind of unsafe, but it turns out that CONTEXT_INFO will eat empty strings and give you back a NULL anyway:

SET @string = LEFT(@string, CHARINDEX(CONVERT(varchar(1), 0x00), @string, 1) - 1);

Or, it turns out you can directly specify a binary value to search for in the REPLACE function:

SET @string = REPLACE(CONVERT(VARCHAR(128), @binary), 0x00, '');

This of course won't trim only trailing null characters, but I assume would be sufficient.

Note: neither of these solutions (nor the one in the comments) work if you switch to using nvarchar(64).

Related Question