alternatives to REPLACE on a text or ntext datatype

Question

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

User · Answer

Assuming SQL Server 2000  the following StackOverflow question should address your problem   If using SQL Server 2005 2008  you can use the following code  taken from here    select cast replace cast myntext as nvarchar max    find   replace   as ntext  from myntexttable

User · Answer

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

[sql] alternatives to REPLACE on a text or ntext datatype

Examples related to sql

Examples related to sql-server

Examples related to tsql