Replace text or ntext fields in SQL Server

Update table name set text field name = replace ( convert ( varchar ( 8000 ) , text field name ) , 'replacement characters' , 'value replaced with ' )

1. Update ntext:
(1) varchar and nvarchar support replace, so if your text/ntext does not exceed 8000/4000, you can convert it to the first two types before using replace.

Update table name set text field name = replace ( convert ( varchar ( 8000 ) , text field name ) , 'replacement characters' , 'value replaced with ' )

UpdateTable Name SetNtext field name=Replace(Convert(Nvarchar(4000),Ntext field name),'Replacement characters','Replace with value')

(2) If text/ntext exceeds 8000/4000, see the following example.

Declare @ Pos int Declare @ Len int Declare @ STR nvarchar ( 4000 ) Declare @ Des nvarchar ( 4000 ) Declare @ Count Int Set @ Des = '<Requested_amount + 1>' -- Value to be replaced Set @ Len = Len ( @ Des ) Set @ Str = '<Requested_amount>' -- The character to be replaced Set @Count=0-- Count. While 1=1 Begin Select @Pos=Patindex('%'+@Des+'%',Propxmldata) -1 FromTable Name WhereCondition If @ Pos > = 0 Begin Declare @ Ptrval binary ( 16 ) Select @ Ptrval = Textptr ( Field name ) From Table Name Where Condition Updatetext table name . Field name @ Ptrval @ Pos @ Len @ Str Set @ Count = @ Count + 1 End Else Break ; End Select @Count

2. The alter column statement has limitations. For example, you cannot modify the text, image, ntext, or timestamp columns.
Here is an example of modifying the ntext column:

Alter Table TBL Add Newcol ntext Null Go Update TBL Set Newcol= Col Go Exec sp_rename 'Tbl. col' , 'Oldcol' , 'Column' Go Exec sp_rename 'Tbl. newcol' , 'Col' , 'Column' Go Alter Table TBL Drop Column Oldcol Go

The preceding method replaces the old column with a new column to change an ntext column that is not allowed to be null to an ntext column that is allowed to be null (Note: The preceding go cannot be missing ). after the table structure is modified, the persistent metadata of the view will expire due to changes to the basic object on which the view depends. You need to refresh the view by using sp_refreshview (you can use sp_depends to find the relevant view, refresh them one by one through sp_refreshview ).
You can also use the following stored procedure to refresh all views:

Print 'Refreshing all views ...' Declare@Vname sysname Declare refresh_cursor cursorFor SelectNameFromSysobjectsWhereXtype= 'V' Order ByCrdate ForReadOnly Open refresh_cursor Fetch next From Refresh_cursor Into @ Vname While @ @ Fetch_status < > - 1 Begin Exec sp_refreshview @ Vname Print 'View' + @ Vname + 'Refreshed' Fetch next From Refresh_cursor Into @ Vname End Close refresh_cursor Deallocate refresh_cursor