Right Trimming Binary Data in SQL Server

Question

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?

Answer 1

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).

Answer 2

Perform the conversion from binary to varbinary with ANSI_PADDING temporarily OFF:

Modified script:

DECLARE @string varchar(128);
DECLARE @binary binary(128); -- now binary not varbinary
SET @string = 'abcdefg';
SET @binary = CONVERT(binary(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 ANSI_PADDING OFF;
SET @string = CONVERT(varchar(128), CONVERT(varbinary(128), @binary))
SET ANSI_PADDING ON;
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

binary	binary.Length	string	string.Length
0x61626364656667000000...	128	abcdefg	7

db<>fiddle online demo

Explanation

There's a fundamental ambiguity with fixed-length types.

Taking a string example, a char(6) type set to contain the value 'ABC' will be right padded with three spaces to fill the defined fixed length. This is indistinguishable from a value of 'ABC ' stored in the same type.

The ambiguity is whether the original value had one or more trailing spaces, or some or all of these spaces were added as padding. There's just no way to know.

Very early (before 6.5) versions of SQL Server took the view that when converting from a fixed-length type to a variable-length type, the server should assume that any right padding was added to the original value and strip it off.

This turned out to be inconsistent with the SQL Standard so a setting (ANSI_PADDING) was added to comply with the standard behaviour, where padding is preserved.

CONTEXT_INFO is also a bit of an oddity. The documentation and server metadata insist it returns a varbinary(128) value, but the implementation is fixed-length binary(128). That is, the value is always stored and returned padded to 128 bytes with trailing 0x00 bytes as necessary.

The desired behaviour in your case is to strip off these trailing 0x00 bytes. This is not done when ANSI_PADDING is ON. It is also not done when converting from varbinary(128) to varbinary(n) regardless of the setting.

To get the desired trimming, we need to capture CONTEXT_INFO as fixed-length binary(128) then perform a conversion to varbinary with ANSI_PADDING set OFF.

Answer 3

The question is tagged 2008 R2 but worth mentioning for future visitors that from SQL Server 2022 you can specify the characters to RTRIM

DECLARE @string varchar(128)= 'abcdefg';
DECLARE @binary binary(128); -- now binary not varbinary
SET @binary = CONVERT(binary(128), @string) --0x61626364656667000000...
SET @string = RTRIM(@binary, 0x00 );
SELECT
 @binary AS [binary],
 DATALENGTH(@binary) AS [binary.Length], --128 as expected
 @string AS [string],
 DATALENGTH(@string) AS [string.Length] --This is now 7

Fiddle

• Paul White: If you're using a modern version of SQL Server, you'd probably want to use the more powerful SESSION_CONTEXT instead of CONTEXT_INFO

Answer 4

For the record, this will trim the trailing 0x00 and leave the 0x00 in place if they happen in the middle of the data:

cast(substring(CONTEXT_INFO(), 1, len(REPLACE(REPLACE(CONTEXT_INFO(), 0x20, 0x21), 0x00, 0x20))) as varbinary(128))

This gives a result as a varbinary, if you want a string, remove the outer cast

Most of the other solutions either end up removing the 0x00 in the middle, or changing it to a 0x20.