Oracle字符串字符截取成多行

Oracle给定字符串，按照目标字符个数截取成多行，给定字符串：’翻（窗a）出来（又）出来’，实现SQL：

with x as
 (select '翻（窗a）出来（又）出来' name from dual)
select regexp_substr(x.name, '.{1,3}', y.lv,1 ) split_chr
  from x,
       (select level lv from dual
        connect by level <=(select max(length(name))+1-3 level_depth_max from x)) y
 where greatest(length(x.name )+1-3,1) >= y.lv;

修改3/3/3即可;

实际应用：

--1.关键字来源自身名称
--清空表数据
TRUNCATE TABLE DIM_SE_SEARCH_KEY;
--查询数据
SELECT * FROM DIM_SE_SEARCH_KEY where id = '999981';
--大区 TYPE=0 SOURCE=1
select distinct domain_id area_id,domain_name area_name,length(domain_name) lgh from dim_company_se;
--省份 TYPE=1 SOURCE=1
select distinct province_id area_id,province_name area_name,length(province_name) lgh from dim_company_se;
--中心 TYPE=2 SOURCE=1
select distinct CENTER_ID area_id,SITE_ATT area_name,length(SITE_ATT) lgh from dim_company_se;
--网点 TYPE=3 SOURCE=1
select distinct SITE_ID area_id,SITE_NAME area_name,length(SITE_NAME) lgh from dim_company_se;

begin
  --替换最外层循环SQL为：大区、省、中心、网点
  for y1 in (select distinct 0 type,domain_id area_id,domain_name area_name,length(domain_name) lgh from dim_company_se
    union all
    select distinct 1 type, province_id area_id,province_name area_name,length(province_name) lgh from dim_company_se
    union all
    select distinct 2 type, CENTER_ID area_id,SITE_ATT area_name,length(SITE_ATT) lgh from dim_company_se
    union all
    select distinct 3 type, SITE_ID area_id,SITE_NAME area_name,length(SITE_NAME) lgh from dim_company_se
    ) loop
    --根据名称长度，判断循环次数
    for i in 1..y1.lgh loop
      --数据入库
      insert into DIM_SE_SEARCH_KEY
      with x as
           (select y1.area_id,y1.area_name,y1.lgh from dual)
           select distinct
           y1.area_id,y1.area_name,y1.type type,1 source,
           regexp_substr(x.area_name, '.{1,'||i||'}', y.lv,1 ) key,
           sysdate update_date
           from x,
           (select level lv from dual
           connect by level <=(select max(length(area_name))+1-i level_depth_max from x)) y
           where greatest(length(x.area_name )+1-i,1) >= y.lv;
    end loop;
  end loop;
end;

修改：

--1.关键字来自自身名称
--清空表数据
TRUNCATE TABLE DIM_SE_SEARCH_KEY;
--查询数据
SELECT * FROM DIM_SE_SEARCH_KEY where id = '999981';
--大区 TYPE=0 SOURCE=1
select distinct domain_id area_id,domain_name area_name,length(domain_name) lgh from dim_company_se;
--省份 TYPE=1 SOURCE=1
select distinct province_id area_id,province_name area_name,length(province_name) lgh from dim_company_se;
--中心 TYPE=2 SOURCE=1
select distinct CENTER_ID area_id,SITE_ATT area_name,length(SITE_ATT) lgh from dim_company_se;
--网点 TYPE=3 SOURCE=1
select distinct SITE_ID area_id,SITE_NAME area_name,length(SITE_NAME) lgh from dim_company_se where center_type='网点';

begin
  --替换最外层循环SQL为：大区、省、中心、网点
  for y1 in (select distinct 0 type,domain_id area_id,domain_name area_name,length(domain_name) lgh from dim_company_se
    union all
    select distinct 1 type, province_id area_id,province_name area_name,length(province_name) lgh from dim_company_se
    union all
    select distinct 2 type, CENTER_ID area_id,SITE_ATT area_name,length(SITE_ATT) lgh from dim_company_se where center_type = '中心'
    union all
    select distinct 3 type, SITE_ID area_id,SITE_NAME area_name,length(SITE_NAME) lgh from dim_company_se where center_type = '网点'
    ) loop
    --根据名称长度，判断循环次数
    for i in 1..y1.lgh loop
      --数据入库
      insert into DIM_SE_SEARCH_KEY
      with x as
           (select y1.area_id,y1.area_name,y1.lgh from dual)
           select distinct
           y1.area_id,y1.area_name,y1.type type,1 source,
           regexp_substr(x.area_name, '.{1,'||i||'}', y.lv,1 ) key,
           sysdate update_date
           from x,
           (select level lv from dual
           connect by level <=(select max(length(area_name))+1-i level_depth_max from x)) y
           where greatest(length(x.area_name )+1-i,1) >= y.lv;
    end loop;
  end loop;
end;

--2.关键字来源于ID
select * from DIM_SE_SEARCH_KEY;
select * from dim_company_se;

insert into DIM_SE_SEARCH_KEY
select distinct domain_id ID,domain_name NAME,0 type,0 SOURCE,domain_id KEY,sysdate update_date from dim_company_se
union
select distinct province_id ID,province_name NAME,1 type,0 SOURCE,province_id KEY,sysdate update_date  from dim_company_se
union
select distinct CENTER_ID ID,SITE_ATT NAME,2 type,0 SOURCE,CENTER_ID KEY,sysdate update_date  from dim_company_se where center_type='中心'
union
select distinct SITE_ID ID,SITE_NAME NAME,3 type,0 SOURCE,SITE_ID KEY,sysdate update_date  from dim_company_se where center_type='网点';
commit;

--3.关键字来源于全拼、简拼
select * from DIM_SE_SEARCH_KEY;

--全拼
insert into DIM_SE_SEARCH_KEY
select distinct domain_id ID,domain_name NAME,0 type,2 SOURCE,HZPY.GETHZFULLPY(domain_name) KEY,sysdate update_date from dim_company_se
union
select distinct province_id ID,province_name NAME,1 type,2 SOURCE,HZPY.GETHZFULLPY(province_name) KEY,sysdate update_date  from dim_company_se
union
select distinct CENTER_ID ID,SITE_ATT NAME,2 type,2 SOURCE,HZPY.GETHZFULLPY(SITE_ATT) KEY,sysdate update_date  from dim_company_se
union
select distinct SITE_ID ID,SITE_NAME NAME,3 type,2 SOURCE,HZPY.GETHZFULLPY(SITE_NAME) KEY,sysdate update_date  from dim_company_se where center_type = '网点';
commit;

--简拼
insert into DIM_SE_SEARCH_KEY
select distinct domain_id ID,domain_name NAME,0 type,2 SOURCE,HZPY.HZPYCAP(domain_name) KEY,sysdate update_date from dim_company_se
union
select distinct province_id ID,province_name NAME,1 type,2 SOURCE,HZPY.HZPYCAP(province_name) KEY,sysdate update_date  from dim_company_se
union
select distinct CENTER_ID ID,SITE_ATT NAME,2 type,2 SOURCE,HZPY.HZPYCAP(SITE_ATT) KEY,sysdate update_date  from dim_company_se
union
select distinct SITE_ID ID,SITE_NAME NAME,3 type,2 SOURCE,HZPY.HZPYCAP(SITE_NAME) KEY,sysdate update_date  from dim_company_se where center_type = '网点';
commit;