SQL Bulk Modify Order number

Example: Now there is a requirement to replace the date 20170599 in the order number with the new date 20180699

Before modification:

SELECT * from Icstockbill where Fbillno like ' whfc201705% ' ORDER by Fbillno

Results:

Finterid FDate Fbillno

209234 2017-04-30 00:00:00.000 WHFC201705990001

209235 2017-04-30 00:00:00.000 WHFC201705990002

209236 2017-04-30 00:00:00.000 WHFC201705990003

209237 2017-04-30 00:00:00.000 WHFC201705990004

.......

209239 2017-04-30 00:00:00.000 WHFC2017059900999

After modification

With the Replace function:

Format: repalce (field name, value to replace, new value after replacement)

If you replace a specific value, you can replace the field with the corresponding value, or specify it by condition.

Update Icstockbill Set Fbillno=replace (Fbillno, ' 20170508 ', ' 20180510 ') where Fbillno between ' WHFC201705080001 ' and ' WHFC201705089999 '

Finterid FDate Fbillno

209234 2017-04-30 00:00:00.000 WHFC201806990001

209235 2017-04-30 00:00:00.000 WHFC201806990002

209236 2017-04-30 00:00:00.000 WHFC201806990003

209237 2017-04-30 00:00:00.000 WHFC201806990004

......

209239 2017-04-30 00:00:00.000 WHFC201806990999

In order to achieve this function, Baidu also walked not detours, special leave this article, only for record, hope to help people who need.

SQL Bulk Modify Order number