Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top


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.


DECLARE @string VARCHAR(128)

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

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

    @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


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

share|improve this question

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

share|improve this answer
Quick way to only trim the trailing nulls: SET @string = RTRIM(REPLACE(CONVERT(VARCHAR(128), @binary), 0x00, 0x20)) – db2 Jan 4 '13 at 19:40
@db2: Yep, that would work, too. Looks like there's a few different ways to go about doing it. – Jon Seigel Jan 4 '13 at 19:43
Does seem a real omission that RTRIM doesn't do this. ANSI_PADDING is deprecated or it would be possible to achieve it by inserting into a VARBINARY(128) column. – Martin Smith Jan 4 '13 at 20:20
@Martin: Kinda, sorta, IMO. In any programming language I've worked with, strings are null-terminated, so if anything, I think the conversion to varchar from a binary seems broken. That said, since it is possible to have nulls in a string, the trim functions should probably handle it, too. – Jon Seigel Jan 4 '13 at 22:30

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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