Ways to bulk replace strings in SQL Server

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

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)

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.downcc.com to http://www.downcc.com, the replacement method is:

Update News

Set content = Replace (cast (content as varchar (8000)),

' Www.downcc.com ',

' Http://www.downcc.com ')

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 to generate static time, you can replace the output, there is a seemingly good solution.