一些有用的sql（2）

1, 修改name倒数17到倒数3位符合条件的name

update itcpn_adv_position
   set name = replace(name, substr(name, -17,15), '内容页_banner_页面D区_1'), code=replace(code, 'ITCPN-C', 'ITCPN-D'),page_field= 1102302
 where website_id in
       (select id
          from itcpn_site s
         where (s.group_num = 9 or s.group_num = 4))
   and  substr(name,-17,15) = '内容页_banner_页面C区_2'

 

2,

 

 update xmj_test
                          set html = replace(html,
                                            'http://219.239.88.158/event.ng/Type=click&FlightID=$FlightID$&AdID=$AdID$&TargetID=$TargetID$&Segments=$Segments$&Targets=$Targets$&Values=$Values$&RawValues=$RawValues$&Redirect=',

'[ASCLICK]&u');

避免$和&当特殊符合处理，吧字符串断开
'http://219.239.88.158/event.ng/Type=click'||'&'||'FlightID=$FlightI'