为什么在SQL Server中将CONVERT字符串转换为VARBINARY仅转换第一个字符? [英] why is CONVERT string to VARBINARY in SQL Server only converting first character?

问题描述

我正在使用NLog登录我的应用程序，并且在此过程中，我们正在记录客户编号，该客户编号是C#中的字符串，而数据库中是varbinary(32).我将以下SQL代码用于此特定参数. SQL语句的其余部分工作正常:

I am using NLog to log in my application and as part of that we are logging the customer number, which is a string in C#, and a varbinary(32) in the database. I am using the following SQL code for this specific parameter. The rest of the SQL statement works fine:

CONVERT(varbinary(32), @CustNumber)

和以下NLog参数:

<parameter name="@CustNumber" layout="${event-context:item=CustNumber}" />

和下面的C#代码添加Nlog参数:

and the following code in C# to add the Nlog parameter:

myEvent.Properties.Add("CustNumber", custNumber);

由于某些原因，存储在实际表中的值只是原始客户编号"字符串的第一个字符.我已经进行了两次和三次检查，以确保在将字符串发送到数据库之前不会将其截断.任何帮助将不胜感激.

For some reason the value being stored in the actual table is only the first character of the original Customer Number string. I have double and triple checked to make sure I am not truncating the string before it is sent to the database. Any help would be greatly appreciated.

推荐答案

原因是当您插入时，您正在将Unicode(nvarchar(xx))字符串转换为varbinary.然后，当您选择时，您将转换为varchar(xx).如果您转换为nvarchar(xx)，它将可以正常工作.

The reason is that when you insert you're converting a Unicode (nvarchar(xx)) string to varbinary. Then when you select you're converting to varchar(xx). If you convert to nvarchar(xx) it will work fine.

例如:

• 插入这是一个测试"，因为varbinary(30)结果为0x7468697320697397312012074657374.

• inserting 'this is a test' as varbinary(30) results in 0x7468697320697320612074657374.

插入N'this is a test'，因为varbinary(30)结果为0x74006800690073002000690073002000610020007400650073007400.

inserting N'this is a test' as varbinary(30) results in 0x74006800690073002000690073002000610020007400650073007400.

因此，当您转换回来时，如果您指定varchar(30)，则前00将截断字符串.

So when you convert back, if you specify varchar(30) the first 00 will truncate the string.

这对我来说很好:

delete from Table_2

insert Table_2 (Test) values( CONVERT(varbinary(30), N'this is a test') ) 
select * from Table_2
select CONVERT(nvarchar(30), test) from Table_2

这也是

delete from Table_2

insert Table_2 (Test) values( CONVERT(varbinary(30), 'this is a test') )
select * from Table_2
select CONVERT(varchar(30), test) from Table_2

这篇关于为什么在SQL Server中将CONVERT字符串转换为VARBINARY仅转换第一个字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！