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;