How to remove trailing CHAR(0) from VARCHAR() in SQL Server?

In Microsoft SQL Server, I tried to pass a varchar() string to a trigger by storing varbinary() to context info.

Passing data via CONTEXT_INFO itself is not the problem.

My problem is, that I have to convert a varchar() to varbinary() to be able to pass the data to context info, and the convert back to varchar() produces a trailing CHAR(0) characters, that I can not properly handled anymore.

I tried to find the first index of the CHAR(0) to substring to a new proper string, without success. Also other built-in functions are not able to handle CHAR(0) chars properly.

But:

• LEN() => returns always 128,
• CHARINDEX() => does not find CHAR(0),
• RTRIM() => does not trim non printable CHAR(0) chars,
• REPLACE() => does not replace CHAR(0),

• exception on executing:

  SELECT * FROM (SELECT (@textbin) AS [blabla]) AS [result] FOR XML 

Specially I need the passed string, as plain text, without trailing blanks/CHAR(0) to put the string to xml.

PS.: the BINARY BASE64 representation in xml is not an option I can use!

Here a test SQL script

DECLARE @text VARCHAR(128) DECLARE @bintext BINARY(128) DECLARE @textbin VARCHAR(128) SET @text = 'test' --SET @text = 'test' + CHAR(0) --SET @text = 'test' + CHAR(0) + CHAR(1) SET @bintext = CONVERT(BINARY(128), @text) SET @textbin = CONVERT(VARCHAR(128), @bintext) SET CONTEXT_INFO @bintext SET @textbin = CAST(CONTEXT_INFO() AS VARCHAR(128)) SELECT @text AS [content], LEN(@text) AS [len], DATALENGTH(@text) AS [datalength] , REVERSE(@text) AS [reverse] , LEN(RTRIM(@text)) AS [len_rtrim] , CHARINDEX(CHAR(0), @text) AS [charindex] , (SELECT * FROM (SELECT @text AS [text]) AS [result] FOR XML AUTO, BINARY BASE64) AS [xml] SELECT @bintext AS [content], LEN(@bintext) AS [len], DATALENGTH(@bintext) AS [datalength] , REVERSE(@bintext) AS [reverse] , CHARINDEX(CHAR(0), @bintext) AS [charindex] , (SELECT * FROM (SELECT @bintext AS [bintext]) AS [result] FOR XML AUTO, BINARY BASE64) AS [xml] SELECT @textbin AS [content], LEN(@textbin) AS [len], DATALENGTH(@textbin) AS [datalength] , REVERSE(@textbin) AS [reverse] , LEN(LTRIM(@textbin)) AS [len_rtrim] , CHARINDEX(CHAR(0), @textbin) AS [charindex] , (SELECT * FROM (SELECT REPLACE(@textbin, CHAR(0), '?') AS [textbin]) AS [result] FOR XML AUTO, BINARY BASE64) AS [xml] IF @textbin = @text SELECT '(@textbin = @text) => TRUE' AS [if] ELSE SELECT '(@textbin = @text) => FALSE' AS [if] IF @textbin = 'test' SELECT '(@textbin = ''test'') => TRUE' AS [if] ELSE SELECT '(@textbin = ''test'') => FALSE' AS [if] IF @textbin = 'test' + CHAR(0) + CHAR(1) SELECT '(@textbin = ''test'' + CHAR(0) + CHAR(1)) => TRUE' AS [if] ELSE SELECT '(@textbin = ''test'' + CHAR(0) + CHAR(1)) => FALSE' AS [if] IF @textbin LIKE 'test' SELECT '(@textbin LIKE ''test'') => TRUE' AS [if] ELSE SELECT '(@textbin LIKE ''test'') => FALSE' AS [if] IF @textbin LIKE 'test%' SELECT '(@textbin LIKE ''test%'') => TRUE' AS [if] ELSE SELECT '(@textbin LIKE ''test%'') => FALSE' AS [if] SELECT * FROM (SELECT (@textbin) AS [context_info]) AS [test] FOR XML AUTO, BINARY BASE64 -- SELECT * FROM (SELECT (@textbin) AS [context_info]) AS [test] FOR XML AUTO, TYPE, BINARY BASE64 -- SELECT * FROM (SELECT (@textbin) AS [context_info]) AS [test] FOR XML AUTO -- relatet to "@textbin" -- LEN() => returns always 128, -- CHARINDEX() => does not find CHAR(0), -- RTRIM() => does not trim non printable CHAR(0) chars, -- REPLACE() => does not replace CHAR(0), -- exception on executing: SELECT * FROM (SELECT (@textbin) AS [context_info]) AS [test] FOR XML AUTO, TYPE, BINARY BASE64 -- exception on executing: SELECT * FROM (SELECT (@textbin) AS [context_info]) AS [test] FOR XML AUTO