Methods for batch substitution of strings in SQL Server by MSSQL batch substitution statements

Method One: (This is the most commonly used, because many large sections of content use the text ntext and other data types, and we usually also replace the contents)

The varchar and nvarchar types are support replace, so if your text is no more than 8000, you can first convert to the previous two types before using replace

The statement that replaces the text ntext data type field

Copy CodeThe code is as follows:
Update table name set field name =replace (CAST (same as previous field name as varchar (8000)), ' original content ', ' want to replace as '



method Two: (Replaces other data type field's statement, the actual application is not common)

Copy CodeThe code is as follows:
Update [table name] SET field name = Replace (same as previous field name, ' original content ', ' want to replace what ')

The following are supplementary:
1. How to bulk replace the data in the ntext field
Problem Description:
I want to replace some characters in the field content in the news table in the database in batches.
My content field is of type ntext.

I want to replace the field is the Content field, I want to replace the inside of the www.jb51.net to Http://www.jb51.net, the replacement method is:

Update News
Set content = Replace (cast (content as varchar (8000)),
' Www.jb51.net ',
' Http://www.jb51.net ')

2. How to bulk replace varchar and nvarchar types
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 '

However, the above method, for text or ntext field more than 8000 time can not be used, can generally use ASP program to achieve, first read the contents of the replacement, save to the database.

Temporary solution: is in the generation of static time, you can replace the output, here is a seemingly good solution, we can look at the next document AH.

If a friend who is unfamiliar with SQL Server can use software to implement
SQL Server database Bulk replacement tool (database and text file lookup replacement) v1.0 Chinese Green Edition

SQL Server database Bulk find substitution tool 1.2 sql Trojan Cleanup assistant