How to replace Data in batches in SQL Server databases

In SQL Server database operations, we may replace Data in batches based on certain write needs. How can we modify and replace Data in batches? In this article, we will introduce this part of content. Next let's take a look at it ......

In SQL Server database operations, we may replace Data in batches based on certain write needs. How can we modify and replace Data in batches? This article introduces this part of content. Let's take a look at it.

Method 1:

This is the most commonly used, because many of the content in a large segment uses data types such as text ntext, and we usually Replace the content. The varchar and nvarchar types support replace, therefore, if your text does not exceed 8000, you can convert it to the first two types before using replace to replace the Statement of the text ntext Data Type field.

CopyCodeAs follows:

Update table name set field name = Replace (cast (same as the previous field name as varchar (8000), 'original content', and 'What to replace ')

Method 2:

The statement that replaces other data type fields is not common in actual application. The copy code is as follows:

Update [Table name] Set field name = Replace (same as the previous field name, 'original content', and 'What to replace ')

Notes To be supplemented:

1. How to replace Data in the ntext field in batches? Problem description: I want to replace some characters in the content field of the news table in the database in batches. My content field is ntext type. The field I want to replace is the content field. I want to replace www.jb51.net with the http://www.jb51.net:

The following is a code snippet: Update News set content = Replace (cast (content as varchar (8000), 'www .jb51.net', 'HTTP: // www.jb51.net ')

 

2. How to replace varchar and nvarchar types in batches

Varchar and nvarchar support replace, so if your text/ntext cannot 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 ')

However, the above method cannot be used when the text or ntext field exceeds 8000. aspProgramAnd then save the read content to the database.