Concatenate ntext in SQL Server 2005

sql-server sql-server-2005 ntext
MariusCC picture MariusCC · Aug 13, 2009 · Viewed 13.9k times · Source

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

marc_s picture marc_s · Aug 13, 2009 
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

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More