例:现在有个需求,需要将一批订单号的中的日期20170599 替换成新的日期20180699
修改前:
select * from ICStockBill where FBillNo like 'WHFC201705%' order by FBillNo
结果:
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
修改后
用replace函数:
格式: repalce(字段名,要替换的值,替换后的新值)
如果替换的是具体的值可以把字段替换成相应的值,或通过条件指定。
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
为了实现这个功能,百度后也走了不走弯路,特留此文,仅作记录,希望帮到有需要的人。
转载于:https://blog.51cto.com/752030200/1945339