为什么SQL要截断我的数据?

I am trying to insert data into a field defined as nvarchar(1000), using SSMS (SQL Server 2008). It will not allow me to enter a literal 1000 characters long. I get the message:

我正在尝试使用SSMS (SQL Server 2008)将数据插入到定义为nvarchar(1000)的字段中。它不允许我输入1000个字符。我得到消息:

Msg 8152, Level 16, State 4, Line 1 String or binary data would be truncated. The statement has been terminated.

Msg 8152，级别16，状态4，第一行字符串或二进制数据将被截断。声明已被终止。

If I shorten it to 850 characters, it does the insert, but using LEN() reveals that it has truncated the data to just 749 characters.

如果我将它缩短为850个字符，它就会执行插入操作，但是使用LEN()会显示它将数据截断为仅749个字符。

I really need my 1000 characters. How can I load this data?

我真的需要1000个字符。如何加载这些数据?

Here is my script:

这是我的脚本:

    INSERT INTO [OnsiteV3].[dbo].[SQLPendingEvent]
       ([KeyDeviceID]
       ,[UpdateTime]
       ,[EventClass]
       ,[EventCode]
       ,[EventType]
       ,[EventDateTime]
       ,[KeyPropertyID]
       ,[KeyEntityID]
       ,[EventText]
       ,[KeyUserID]
       ,[EventImage]
       ,[TaskType]
       ,[TaskSubType]
       ,[UniqueTaskID])
 VALUES
       ('SVS-IMPL-DARREN'
       ,GETDATE()
       ,'REPAIR'
       ,'NOTE'
       ,'ADD'
       ,GETDATE()
       ,1003443
       ,10812
       ,'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       '
       ,'DARRENWORK'
       ,NULL
       ,NULL
       ,NULL
       ,'hsg00010812000P')

GO

去

3 个解决方案

#1

Not sure if this is only due to formatting here on SO, but to me it looks like you are trying to insert about 80 characters of whitespace into the field as well, bringing your total character count up to 1080.

不确定这是否只是由于这里的SO格式，但对我来说，似乎您也在尝试向字段中插入大约80个空格字符，使您的总字符数达到1080。

Also, LEN does not count trailing whitespace (yes, this is awful).

而且，LEN并不计算后面的空格(是的，这很糟糕)。

#2

I really need my 1000 characters. How can I load this data?

我真的需要1000个字符。如何加载这些数据?

One solution/workaround would be to remove the spaces, carriage returns and line feeds from the string:

一种解决方案是从字符串中删除空格、回车和换行:

replace(replace(replace( <your string> ', ' ', ''), char(10),''), char(13), '')

Applying this to your sample data (with blanks) makes it possible to insert into nvarchar(1000).

将此应用于示例数据(带有空格)可以插入到nvarchar(1000)中。

If you want to keep the string as is, with blanks andCR/LF, the obvious solution would be to alter the table to allow longer strings, maybe to nvarchar(max).

如果想让字符串保持原样，使用空格和cr /LF，显然的解决方案是修改表以允许更长的字符串，可能是nvarchar(max)。

Sample SQL Fiddle

示例SQL小提琴

#3

I have been using a user defined function for this purpose.

为此，我一直在使用用户定义的函数。

http://blog.sqlauthority.com/2008/10/10/sql-server-2008-enhenced-trim-function-remove-trailing-spaces-leading-spaces-white-space-tabs-carriage-returns-line-feeds/

http://blog.sqlauthority.com/2008/10/10/sql服务器- 2008 -让-减少-功能-删除-落后-空间-领先的空间-白-空间-选项卡-运输-返回-线- feeds/