oracle 分割,号

来源：互联网发布：淘宝上回收黄金靠谱吗编辑：程序博客网时间：2024/06/05 11:37

select "ID","START_IP","END_IP" from (with temp0 as (select LEVEL lv from dual CONNECT BY LEVEL <= 100)
     select ID,substr(t.vals,instr(t.vals, ',', 1, tv.lv) + 1,
                       instr(t.vals, ',', 1, tv.lv + 1) -(
                     instr(t.vals, ',', 1, tv.lv) + 1)
               ) AS start_ip ,
               substr(t.vals1,instr(t.vals1, ',', 1, tv.lv) + 1,
                       instr(t.vals1, ',', 1, tv.lv + 1) -(
                     instr(t.vals1, ',', 1, tv.lv) + 1)
               ) AS end_ip
    from (select ID,',' || start_ip || ',' AS vals,
                length(start_ip || ',') - nvl(length(REPLACE(start_ip, ',')), 0) AS cnt ,
                ',' || end_ip || ',' AS vals1,
                length(end_ip || ',') - nvl(length(REPLACE(end_ip, ',')), 0) AS cnt1

          from HOST_WEBSITE_INFO) t join temp0 tv

on tv.lv <= t.cnt order by 1)

注：功能分割start_ip，end_ip

0 0