Modify, add, and delete field content using SQL bulk substitution statements _mssql

SQL substitution statement, which allows you to replace the contents of a field in batches, or to add or remove characters from the contents of the original field.

Command Total Solution: Name of the Update table set this table to replace the field name =replace (this table to replace the field name, ' original content ', ' new content ')

such as UPDATE Whir_productrelese SET relesename=replace (relesename, ' http://www.maidq.com ', ' http://maidq.com ')

An example is provided:

1 The Backupfile table URL in the field content for the http://www.maidq.com of the characters all changed to http://maidq.com.

Update backupfile set url=replace (URL, ' http://www.maidq.com ', ' http://maidq.com ')

2 Add the contents of the field according to the condition, such as adding TMP to the contents of the Logical_name field of the File_number=1 record, followed by the end.

Update backupfile set Logical_name=replace (logical_name,logical_name, ' tmp ' +logical_name+ ' End ') where file_number=1

3 The first 2 characters of the specified record are removed according to the condition.

Update backupfile Set Logical_name=replace (logical_name,logical_name,substring (Logical_name,3,len (logical_name)-2 ) Where file_number=1

4 Remove the following 4 characters from the specified record according to the condition.

Update backupfile Set Logical_name=replace (logical_name,logical_name,substring (Logical_name,1,len (logical_name)-4 ) Where file_number=2

If you are not clear, you can use the SELECT statement to verify that you have achieved the desired effect before you replace it:

SELECT Replace (replace field, ' original content ', ' new content ') from table name; 
Update table name set replace field = (replace field, ' original content ', ' new content ')

The above is a small set for you to introduce the use of SQL batch replacement statements to modify, add, delete the field content, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!