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.
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?
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.
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?
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.
