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.
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:
Is it really only 250 characters long???
Also: you're mixing
VARCHAR
(in@my_string
) andNVARCHAR
(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:
As I said - in my tests, I didn't need to do this - but maybe it works in your case?
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 to256
.Once I set this to
1000
the problem was fixed.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?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.