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

Question

In Microsoft SQL Server, I tried passing a string varchar()to a trigger by writing varbinary()to the context information.

Transferring data through CONTEXT_INFOitself is not a problem.

My problem is that I need to convert varchar()to varbinary()to pass data to contextual information, and converting back to varchar()creates trailing characters CHAR(0)that I may not be able to handle properly.

I tried to find the first CHAR(0)substring index in the new correct row without success. In addition, other built-in functions cannot handle characters correctly CHAR(0).

But:

• LEN() => always returns 128,
• CHARINDEX()=> Can not find CHAR(0),
• RTRIM()=> does not crop non-printable characters CHAR(0),
• REPLACE()=> Does not replace CHAR(0),

• exception on execution:

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

Specifically, I need a passed string, like plain text, without trailing spaces / CHAR (0) to put the string in xml.

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

Here's 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

Answer 1

As Dark noted

this is a duplicate of the question: What is a null character literal in TSQL?

... thanks to show me.

this change fixed my code:

SET @textbin = REPLACE(CAST(CAST(CONTEXT_INFO() AS VARCHAR(128)) COLLATE SQL_Latin1_General_CP1_CI_AS AS VARCHAR(128)), CHAR(0), '')