Concatenate ntext in SQL Server 2005

Question

I need to concatenate 2 ntext columns into one. I can't convert them to nchar, cause both contains strings longer than 4000 chars. Is there a way to do this in SQL Server 2005?

Answer 1

UPDATE 
    YourTable
SET 
    Field = CAST( (CAST(field1 AS NVARCHAR(MAX)) + CAST(field2 AS NVARCHAR(MAX))) AS NTEXT)
WHERE 
    (your condition here)

But really - with SQL Server 2005, NTEXT becomes deprecated and will most likely be phased out in SQL Server 2008 R2 or one release later. NVARCHAR(MAX) is the logical successor, giving you all NTEXT ever gave you, and a lot more!

If your fields would be NVARCHAR(MAX) from the beginning, you could just write:

UPDATE 
    YourTable
SET 
    field = field1 + field2
WHERE 
    (your condition here)

and be done with it!

I'd suggest you upgrade your tables to use NVARCHAR(MAX) instead of NTEXT.

Marc

Answer 2

There is a way to update ntext column:

DECLARE @memo binary(16)

SELECT 
    @memo = TEXTPTR(field1)
FROM 
    YourTable
WHERE 
    (your condition here)

UPDATETEXT YourTable.field1 @memo NULL 0 'Text to append'

Here are more information.

Answer 3

Convert them to nvarchar(max) for the concatentation. It's the SQL 2005 replacement for ntext and allows all the usual nvarchar operations.