alternatives to REPLACE on a text or ntext datatype

I need to update/replace the data in datatable.column. The table has a field named Content. I'm using the REPLACE function. Since the column datatype is NTEXT, SQL Server doesn't allow me to use the REPLACE function.

I can't change the datatype because this database is 3rd party software table. Changing the datatype will cause the application to fail.

UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
SET Content = REPLACE(Content,'ABC','DEF') 
WHERE Content LIKE '%ABC%' 

I Receive this error:

Msg 8116, Level 16, State 1, Line 1 Argument data type ntext is invalid for argument 1 of replace function.

  • Can I fix this with T-SQL? Does someone have an example how to read and to loop?
  • Since this is onetime conversion, maybe I can change to another type but I'm afraid I'm messing up the data.

There is a primary key field: name: ID - integer - it's an identity.... So I need to think about this too. Maybe set the Identity to N temporary.

Please advise on how to achieve the REPLACE function?

Approx. 3000 statements need to be updated with a new solution.

Answers


IF your data won't overflow 4000 characters AND you're on SQL Server 2000 or compatibility level of 8 or SQL Server 2000:

UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
SET Content = CAST(REPLACE(CAST(Content as NVarchar(4000)),'ABC','DEF') AS NText)
WHERE Content LIKE '%ABC%' 

For SQL Server 2005+:

UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
SET Content = CAST(REPLACE(CAST(Content as NVarchar(MAX)),'ABC','DEF') AS NText)
WHERE Content LIKE '%ABC%' 

Need Your Help

How to map existing folders (which are moved to new machine) with the TFS without need to download the entire data from TFS?

visual-studio-2010 tfs2010 vsts2010 workspace

Earlier I had mapped my local folders to the TFS. I had to change machine/computer for some reason. So I copied all the folders to the new machine.

Troubleshooting 'can only concatenate tuple (not “str”) to tuple' error

python django django-admin

when I try to extend the django admin user. I got the following error.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.