replace ntext (more than 4000 characters) in sql server 2000

Question

How to replace text in sql server 2000 in a ntext column with more than 4000 characters? conversion to nvarchar(max) does not work as it truncates values.

Answer 1

nvarchar(max) isn't supported until sql server 2005. For sql server 2000 columns with more than 4000 wide characters, you must pull the data down to a client application and make your changes there.

Answer 2

Working with TEXT/NTEXT is a mess - one of the many reason to get rid of those fields as quickly as possible.

You need to manipulate those with command such as READTEXT, WRITETEXT, UPDATETEXT and other - see some resources for help:

• MSDN docs on READTEXT
• MSDN docs on WRITETEXT
• MSDN docs on UPDATETEXT
• How do I handle REPLACE() within an NTEXT column in SQL Server?
• Update Text, NText Columns using UPDATETEXT Command in MS SQL Server

Answer 3

This page sketches a solution using PATINDEX(), TEXTPTR() and UPDATETEXT