SQL Server database content replacement method

In the process of using the iwms system, we often encounter data content replacement operations. Before telling you how to replace the data content, I suggest you first understand the data storage type of the SQL Server database:

SQL Server Data Type:

The above is the basic knowledge of the database, and the content that all website friends should know, no matter what cms you use), so we suggest you take a look at it with patience.

Data replacement generally occurs in string data fields. Other string data fields except ntext fields can be replaced by the following SQL statement:

update [swf_Upload] set [Dir] = replace([Dir],'200901/14','200901/15') update [swf_Content] set [Description] = replace([Description],'200901/14','200901/15') update [swf_Content_01] set [content] = replace(convert(varchar(4000), [content]),'200901/14','200901/15')

UPDATE [data table name] SET [field name] = REPLACE ([field name], 'old string', 'new string ')
For example, replace part of the title field title in the iwms document data table iwms_news). We should write it like this:

UPDATE [iwms_news] SET [title] = REPLACE ([title], 'old string', 'new string ')
The preceding SQL statement can be directly executed in the iwms background SQL Execution, basically all replacement operations can be done, but due to the ntext Data Length, this method is invalid for ntext fields. So what method should we use to replace the content of the ntext field? There are two methods:

First, type conversion: Convert the ntext type to the varchar type, and then use replace. Suitable for articles with a maximum length of <4000 per page.

Update [data table name] set [field name] = replace (convert (varchar (4000), [field name]), 'old string', 'new string ')
For example, replace part of the content field of the title field in the iwms document data table iwms_news) with the following content:

Update iwms_news set [content] = replace (convert (varchar (4000), [content]), 'old string', 'new string ')

Second, SQL Server Stored Procedures

Declare @ ptr varbinary (16)
Declare @ artId int
Declare @ Position int, @ len int
Set @ len = datalength ('old string ')
Declare wux_Cursor scroll Cursor
For
Select textptr ([field name]), [key field name] from [Table name]
For read only
Open wux_Cursor
Fetch next from wux_Cursor into @ ptr, @ artId
While @ fetch_status = 0
Begin
Select @ Position = patindex ('% old string %', [field name]) from [Table name] where [key field name] = @ artId
While @ Position> 0
Begin
Set @ Position = @ Position-1
Updatetext [data table name]. [field name] @ ptr @ Position @ len 'new string'
Select @ Position = patindex ('% old string %', [field name]) from [Table name] where [key field name] = @ artId
End
Fetch next from wux_Cursor into @ ptr, @ artId
End
Close wux_cursor
Deallocate wux_cursor
Go

For example, replace part of the content field of the title field in the iwms document data table iwms_news) with the content field of the ntext type.

Declare @ ptr varbinary (16)
Declare @ artId int
Declare @ Position int, @ len int
Set @ len = datalength ('old string ')
Declare wux_Cursor scroll Cursor
For
Select textptr ([content]), [articleid] from iwms_news
For read only
Open wux_Cursor
Fetch next from wux_Cursor into @ ptr, @ artId
While @ fetch_status = 0
Begin
Select @ Position = patindex ('% old string %', [content]) from iwms_news where [articleid] = @ artId
While @ Position> 0
Begin
Set @ Position = @ Position-1
Updatetext iwms_news. [content] @ ptr @ Position @ len 'new string'
Select @ Position = patindex ('% old string %', [content]) from iwms_news where [articleid] = @ artId
End
Fetch next from wux_Cursor into @ ptr, @ artId
End
Close wux_cursor
Deallocate wux_cursor
Go

OK. Note that the stored procedure can only be executed in the SQL Server Query analyzer.

In addition, due to the structure of the iwms database, the content of the paging articles must be replaced by the content of the iwms_news and iwms_pages tables.

1. Effective use of the automatic management function of SQL Server
2. Migrate SQL 2000 logs to SQL Server 2008
3. Upgrade SQL Server 2008 Database Engine