以逗号为分割将字符串分割多个列 | regexp_substr

以逗号为分割将字符串分割多个列。

1、语法：

select regexp_substr('a,b,c,', '[^,]+', 1, rownum)
  from dual
connect by rownum <= length(regexp_replace('a,b,c,', '[^,]', null));

 

2、实例：

update t_wf_proc_instance wf
   set wf.subarea_no = 99
 where wf.ext_work_id in
       (select od.order_id
          from t_od_order od
         where od.product_num in
               (select regexp_substr((select a.config_condition
                                       from t_od_busi_conf a
                                      where a.config_name =
                                            'app_product_num_str'),
                                     '[^,]+',
                                     1,
                                     rownum)
                  from dual
                connect by rownum <= length(regexp_replace((select a.config_condition
                                                             from t_od_busi_conf a
                                                            where a.config_name =
                                                                  'app_product_num_str'),
                                                           '[^,]',
                                                           null)))
           and od.create_date >
               To_Date('2017-11-21 18:00', 'yyyy-mm-dd hh24:mi'));
commit;