How to replace strings in SQL SERVER with a batch replacement Statement of MSSQL

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 in it)

Varchar and nvarchar support replace, so if your text does not exceed 8000, you can convert it to the first two types before using replace

Statement used to replace text ntext Fields

Copy codeThe Code is as 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: (Replace statements of other data type fields, which are not common in actual applications)

Copy codeThe Code is as follows:
Update [Table name] set field name = replace (same as the previous field name, 'original content', and 'What to replace ')

The following is a supplement:
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:

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. Generally, it can be implemented using an asp program. After the content is read and replaced, it is saved to the database.

Temporary solution: it is to generate a static time, which can be replaced before output. Here is a good solution. You can refer to the next file.

If you are not familiar with sqlserver, you can use the software to implement it.
SQL Server database batch replacement tool (database and text file search and replacement) v1.0 Chinese Green Edition

SQL Server database bulk search replacement tool 1.2 SQL Trojan clearing Assistant