Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question

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

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

Your Answer

 
discard

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.