在 SQL Server 中正确修剪二进制数据 [英] Right Trimming Binary Data in SQL Server
问题描述
场景:
我正在将一个字符串插入到一个二进制字段 (CONTEXT_INFO) 中,然后尝试将其拉出并将其转换回一个字符串.当我这样做时,结果字符串的长度为 128,因为它有尾随空字符.
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)
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
问题:
如何在将二进制字段转换为字符串时修剪尾随空字符?
How can I trim the trailing null characters when I convert the binary field to a string?
推荐答案
试试这个,适用于 Sql-Server 2008.
这里是 Sql Fiddle.
Try this, works on Sql-Server 2008.
Here is Sql Fiddle.
请注意,我假设原始字符串中没有 Char(0),因为这甚至可以简单地从原始字符串替换它.
Please note that I am assuming that the original string has NOT got Char(0) in it as this could simply replace it even from the original string.
-- I would like to change the following line so it trims trailing null chars
SET @string = CONVERT(VARCHAR(128), @binary)
SET @string = REPLACE(@string, Char(0),'')
这篇关于在 SQL Server 中正确修剪二进制数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!