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
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.
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%'