sql-server - SQL Server 从 nvarchar 到 varbinary，然后从 varbinary 到 nvarchar 的转换保真度

Question

我正在尝试转换回存储在 CONTEXT_INFO 中的 nvarchar。

declare @LanguageCode nvarchar(6) = 'en';
declare @binvar varbinary(128);
set @binvar = cast(@LanguageCode as varbinary);
set context_info @binvar;

select A = len(@LanguageCode);
select B = len(convert(nvarchar(6),@LanguageCode));

select C = convert(nvarchar(6),context_info());
select D = cast(CONTEXT_INFO() as nvarchar(5));
select E = len(convert(nvarchar(6),CONTEXT_INFO()));
select F =len(cast(CONTEXT_INFO() as nvarchar(6)));

select G = convert(nvarchar,context_info());
select H = cast(CONTEXT_INFO() as nvarchar);
select I = len(convert(nvarchar,CONTEXT_INFO()));
select J = len(cast(CONTEXT_INFO() as nvarchar));

将varbinary转换为nvarchar得到的nvarchar长度为6，它包括\0。例如，语言“en”返回为“en\0\0\0\0”。这搞砸了 NHibernate 上的 key 缓存.

有没有办法可以将存储在 varbinary 中的 nvarchar 值转换回其原始值？即，“en”可以转换回“en”。

输出:

A
-----------
2

B
-----------
2

C
------
en    

D
-----
en   

E
-----------
6

F
-----------
6

G
------------------------------
en                            

H
------------------------------
en                            

I
-----------
30

J
-----------
30

选择答案的理由

我选择了 HABO 的答案，因为他的解决方案可以直接嵌入到消费查询中:

declare @LanguageCode nvarchar(6) = 'en';
declare @binvar varbinary(128);
set @binvar = cast(datalength(@LanguageCode) as varbinary) + cast(@LanguageCode as varbinary);
set context_info @binvar;
select dump = @binvar;



-- directly embeddable
select  recoveredLanguage = convert(nvarchar, substring(context_info(), 5, convert(int, substring(context_info(), 1, 4)) )  ) 
        , theLength =  len ( convert(nvarchar, substring(context_info(), 5, convert(int, substring(context_info(), 1, 4)) )  ) )


declare @buffer varbinary(128) = context_info();
declare @RecoveredLanguageCode nvarchar(6) = cast(substring(@buffer, 5, cast(substring(@buffer, 1, 4) as int)) as nvarchar(6));
select recoveredLanguage = @RecoveredLanguageCode, theLength = len(@RecoveredLanguageCode);

输出:

dump
--------------------
0x0000000465006E00

(1 row(s) affected)

recoveredLanguage              theLength
------------------------------ -----------
en                             2

(1 row(s) affected)

recoveredLanguage theLength
----------------- -----------
en                2

(1 row(s) affected)

虽然 souplex 的回答是正确的，但我不确定 SET ANSI_PADDING_OFF 是全局影响，还是仅对一批语句局部影响。将所有的转换放在一个语句中有副作用，恢复不起作用，每个转换必须在单独的语句上完成。无论如何，我赞成 souplex 的回答

DECLARE @LanguageCode NVARCHAR(6) = 'en';
DECLARE @binvar VARBINARY(128);
SET @binvar = CAST(@LanguageCode AS VARBINARY(128));
SET context_info @binvar;
select dump = @Binvar;




SET ANSI_PADDING OFF;
select recoveredLanguage = cast( cast( cast(context_info() as binary(128)) as varbinary(128) ) as nvarchar(6) )
        , theLength = len( cast( cast( cast(context_info() as binary(128)) as varbinary(128) ) as nvarchar(6) ) );


DECLARE @binvar1 BINARY(128) = context_info();
DECLARE @binvar2 VARBINARY(128) = CAST(@binvar1 AS VARBINARY(128));
declare @c nvarchar(6) = cast(@binvar2 as nvarchar(6))
select recoveredLanguage = @c, theLength = len(@c);

输出:

dump
----------
0x65006E00

(1 row(s) affected)

recoveredLanguage theLength
----------------- -----------
en                6

(1 row(s) affected)

recoveredLanguage theLength
----------------- -----------
en                2

(1 row(s) affected)

感谢 HABO 和 souplex 没有提出 XML PATH 或 XML AUTO 解决方案:-) 谢谢!

Answer 1

在将字符串保存到 CONTEXT_INFO 之前，在字符串前加上长度前缀:

declare @LanguageCode nvarchar(6) = 'en';
declare @binvar varbinary(128);
set @binvar = cast(datalength(@LanguageCode) as varbinary) + cast(@LanguageCode as varbinary);
set context_info @binvar;

declare @buffer varbinary(128) = context_info();
declare @RecoveredLanguageCode nvarchar(6) = cast(substring(@buffer, 5, cast(substring(@buffer, 1, 4) as int)) as nvarchar(6));
select @RecoveredLanguageCode, len(@RecoveredLanguageCode);