Replace text or ntext fields in SQL Server

Replace text or ntext fields in SQL Server

Update table name

Set text field name = replace (convert (varchar (8000), text field name), 'replacement character ', 'replacement to value ')

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 character ', 'replacement to value ')

Update table name

Set ntext field name = replace (convert (nvarchar (4000), ntext field name), 'replacement character ', 'replacement 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 ='

Set @ len = len (@ des)

Set @ str =' '-- The character to be replaced

Set @ count = 0 -- count.

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

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 CURSOR

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