SQL Replacement Statement Bulk Modify, add, delete field contents

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

Command Total Solution: The 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, ' AA ', ' BB ')

To illustrate:

1) Change all the characters in the field contents of the Backupfile table URL to BB.

Update backupfile set url=replace (URL, ' AAA ', ' BBB ')

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

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

3) Remove the first 2 characters of the specified record according to the conditions.

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 conditions.

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

If it is unclear, you can use the SELECT statement to verify that you have achieved your desired effect before replacing it:

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

Update table name set replace field = (replace (replacement field, ' original content ', ' new content ')

SQL Replacement Statement Bulk Modify, add, delete field contents