Shulou(Shulou.com)05/31 Report--

SQL Server database how to replace data in batches, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

The method of replacing data in batch in SQLServer Database

Method 1:

This is the most commonly used, because many large pieces of content use data types such as textntext, and we usually replace the contents inside, varchar and nvarchar types support replace, so if your text is not more than 8000, you can first convert to the first two types and then use replace to replace the textntext data type field statement.

The copy code is as follows:

Update table name set field name = replace (cast (same as before field name asvarchar (8000)), 'original content', 'what do you want to replace with')

Method 2:

Statements that replace fields of other data types are not common in practical applications. The copy code is as follows:

Update [table name] set field name = replace (same field name as before, 'original content', 'what do you want to replace with')

The method of replacing data in batch in SQLServer Database

A few points need to be added:

1. How to batch replace the data in the ntext field, problem description: I want to batch replace some characters in the field content in the News table in the database. My content field is of type ntext. The field I want to replace is the content field. I want to replace the www.jb51.net with http://www.jb51.net. The replacement method is:

UpdateNewssetcontent=replace (cast (contentasvarchar (8000)), 'www.jb51.net',' http://www.jb51.net')

two. How to replace varchar and nvarchar types in batches

Replace is supported for varchar and nvarchar types, so if your text/ntext is no more than 8000 text/ntext 4000, you can convert to the first two types before using replace.

Update table name

Settext type field name = replace (convert (varchar (8000), text type field name), 'character to replace', 'replaced value')

Update table name

Setntext type field name = replace (convert (nvarchar (4000), ntext type field name), 'character to replace', 'replaced value')

However, the above method, for text or ntext field more than 8000 can not be used, generally can be achieved with asp program, first read the content to replace, save to the database.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.