Content substitution method for text or ntext fields in SQL Server

Update table name

Set Text Type field name =replace (CONVERT (varchar (8000), Text Type field name), ' character to replace ', ' replaced by value '

1.update ntext:

(1) The varchar and nvarchar types are support replace, so if your text/ntext is no more than 8000/4000, you can convert to the first two types before using replace.

Update table name

Set Text Type field name =replace (CONVERT (varchar (8000), Text Type field name), ' character to replace ', ' replaced by value '

Update table name

Set ntext Type field name =replace (convert (nvarchar (4000), ntext Type field name), ' character to replace ', ' replaced by value '

(2) If Text/ntext is over 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 = '

Set @len =len (@des)

Set @str = ' --characters to be replaced

Set @count the number of =0--statistics.

While 1=1

BEGIN

Select @pos =patindex ('% ' + @des + '% ', propxmldata)-1

From table name

Where condition

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

The 2.alter column statement has limitations, such as not allowing you to modify text, image, ntext, or timestamp columns.

The following provides 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 above replaces the old column method with a new column to modify a ntext that is not allowed to be empty to a ntext column that is allowed to be empty (note: The above go cannot be missing). After you modify the table structure, the persisted metadata for the view will expire due to changes to the underlying object on which the view depends, and you need to refresh the view by sp_ Refreshview (You can find the relevant view through the sp_depends, and then refresh by Sp_refreshview one by one).

You can also refresh all views by using a stored procedure:

PRINT ' Refreshing all views ... '

DECLARE @vName sysname

DECLARE refresh_cursor Cursor FOR

SELECT Name from sysobjects WHERE xtype = ' V '

ORDER BY Crdate

For READ only

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