9

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MariusCC
  • 617
  • 2
  • 6
  • 11

3 Answers3

12
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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Hi Marc. I don't want to try to convert them because of risk of breaking the app: MS Team Foundation Server. I have enough misery without 'fixing' it. Thanks for your solution. Worked like magic ;). – MariusCC Aug 19 '09 at 09:21
  • Hi @marc_s I think you might have a tiny little typo in your first example, "NEXT" rather than "NTEXT". (Good answer though, +1 from me) – Alex KeySmith Nov 23 '11 at 14:44
  • @AlexKey: yes, absolutely right! Thanks for catching that - updated my response – marc_s Nov 23 '11 at 14:46
  • 1
    Hi Marc_s, If i have field1 as ntext and content is >4000 char. The command CAST(field1 AS NVARCHAR(MAX)) will auto TRIM field1. It not work – culithay Feb 06 '15 at 08:51
3

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

AakashM
  • 62,551
  • 17
  • 151
  • 186
  • Can't convert them because of risk of breaking the app: MS TFS. I'm just trying to hack some fields. – MariusCC Aug 19 '09 at 09:14
1

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.

Boris Tyumnev
  • 71
  • 2