substr,instr,length配合使用截取信息

 一、栗子1：截取指定参数信息

从666666=141;101201=460011020633643;101203=15600222550;，获得结果460011020633643;

select substr(substr(vcpara,
                     instr(vcpara, '101201') + 7,
                     length(vcpara) - instr(vcpara, '101201') + 7),
              0,
              instr(substr(vcpara,
                           instr(vcpara, '101201') + 7,
                           length(vcpara) - instr(vcpara, '101201') + 7),
                    ';')),
       vcpara
  from tneticket_gsm a
 where a.vcwid = '180507701172545'
   and a.vcsvccode = '101261';

　　

二、栗子2：截取重要节点

select a.recv_queue_seq,
       a.order_id,
       a.recv_date,
       b.send_date,
       substr(b.remark,
       instr(b.remark, '<ResultRemark>'),
       instr(b.remark, '</ResultRemark>')-29)
  from t_od_recv_queue_his a, t_od_send_queue b
 where b.status = 3
   and b.ext_system_id = 4
   and a.recv_queue_seq = b.recv_queue_seq
 order by a.recv_date,b.send_date;

　　

三、栗子3：指令拼接（多行指令，本sql含换行）

SELECT  'MOD OCSI:IMSI="' || substr(a.vcpara, instr(a.vcpara, '101201=') + 7, 15) ||
       '",PROV=TRUE,TPLID=41;'||'
'||'MOD TCSI:IMSI="' ||
       substr(a.vcpara, instr(a.vcpara, '101201=') + 7, 15) ||
       '",PROV=TRUE,TPLID=41;'
,a.vccustomer,a.ineid
  FROM tmp_wzh_vccustomer1 a WHERE a.ineid like '1%';