CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005
user1130456
user1130456

Reputation: 51

SQL Server string getting truncated

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.

Upvotes: 1

Views: 7738

Answers (4)

Sean Tavares
Sean Tavares

Reputation: 1

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.

Upvotes: 0

Rich Chapman-Hopkins
Rich Chapman-Hopkins

Reputation: 31

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.

Upvotes: 3

BartekR
BartekR

Reputation: 3947

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?

Upvotes: 0

marc_s
marc_s

Reputation: 754418

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?

Upvotes: 3

Related Questions