如何从SQL Server中的VARCHAR（）中删除尾随CHAR（0）？

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

在Microsoft SQL Server中,我尝试通过将varbinary()存储到上下文信息来将varchar()字符串传递给触发器。

Passing data via CONTEXT_INFO itself is not the problem.

通过CONTEXT_INFO本身传递数据不是问题。

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.

我的问题是,我必须将varchar()转换为varbinary()才能将数据传递给上下文信息,并且转换回varchar()会产生一个尾随的CHAR(0)字符,我无法正确处理了。

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.

我试图找到CHAR(0)的第一个索引,以子串到一个新的正确字符串,但没有成功。其他内置函数也无法正确处理CHAR(0)字符。

But:

LEN() => returns always 128,

LEN()=>总是返回128,

CHARINDEX() => does not find CHAR(0),

CHARINDEX()=>找不到CHAR(0),

RTRIM() => does not trim non printable CHAR(0) chars,

RTRIM()=>不修剪不可打印的CHAR(0)字符,

REPLACE() => does not replace CHAR(0),

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.

特别是我需要传递的字符串,作为纯文本,没有尾随空格/ CHAR(0)将字符串放到xml。

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

PS。:xml中的BINARY BASE64表示不是我可以使用的选项!

Here a test SQL script

这是一个测试SQL脚本

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

1 个解决方案

#1

As Darka noticed,

正如达达注意到的那样

this is a duplicate of question: What is the Null Character literal in TSQL?

这是一个问题的重复:TSQL中的空字符文字是什么?

... thank you 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), '')

#1