SQL Server string getting truncated

py49o6xq 于 2023-10-15 发布在 SQL Server

关注(0)|答案(4)|浏览(143)

I have a column ( col1 ) with nvarchar(max) .

I am trying to do

DECLARE @my_string NVARCHAR(max)
set @my_string = N'test'

UPDATE dbo.tab1 
SET col1 = @my_string + ISNULL(col1, N'')

no luck , I have no idea why it is happening. @marc_s

The string value in col1 getting truncated after 250 characters. This happening in both SQL Server 2005 and 2008.

sql-server

来源：https://stackoverflow.com/questions/8731229/sql-server-string-getting-truncated

4条答案

按热度按时间

mklgxw1f1#

First of all - I'm not seeing this behavior you're reporting. How and why do you think your column gets truncated at 250 characters?? Are you using a tool to inspect the data that might be truncating the output??

You could check the length of the column:

SELECT col1, LEN(col1) FROM dbo.tab1

Is it really only 250 characters long???

Also: you're mixing VARCHAR (in @my_string ) and NVARCHAR ( col1 ) which can lead to messy results. Avoid this!

Next: if you want NVARCHAR(MAX) , you need to cast your other strings to that format.

Try this:

DECLARE @my_string NVARCHAR(200)
set @my_string = N'test'

UPDATE dbo.tab1 
SET col1 = CAST(@my_string AS NVARCHAR(MAX)) + ISNULL(col1, N'')

As I said - in my tests, I didn't need to do this - but maybe it works in your case?

赞(0）回复(0）举报 2023-10-15

vsikbqxv2#

Go to menu - Query --> Query options --> Results --> Text

There is an option Maximun number of characters displayed in each column and mine was defaulted to 256 .

Once I set this to 1000 the problem was fixed.

赞(0）回复(0）举报 2023-10-15

btxsgosb3#

Do you test in SSMS? If so, check in options Query Results > SQL Server > Results to Grid - Maximum characters retrieved > Non XML data . Is there a value 250 or similar?

赞(0）回复(0）举报 2023-10-15

e3bfsja24#

You're seeing this because the max len of your data in col happens to be 250.

ISNULL ( check_expression , replacement_value ) will truncate the replacement_value to the length of check_expression, per:

https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql

Use COALESCE() instead, which avoids this risk.

赞(0）回复(0）举报 2023-10-15