Oracle string character truncated to multiple lines ____oracle

Oracle-given string, truncated to multiple lines according to the number of target characters, given a string: ' Flip (Window a) out (and) out ', implement SQL:

 with X as (SELECT ' Flip (Window a) out (and) come out ' 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;

Modify 3/3/3 can be;

Practical application:

--1. Keyword source self name--clears table data TRUNCATE table Dim_se_search_key;
--Query data SELECT * FROM Dim_se_search_key where id = ' 999981 '; --da qu type=0 source=1 select distinct domain_id area_id,domain_name area_name,length (domain_name) Lgh from Dim_company_se
; --Province type=1 source=1 select distinct province_id area_id,province_name area_name,length (province_name) Lgh from dim_
Company_se;
--Center type=2 source=1 SELECT distinct center_id area_id,site_att area_name,length (Site_att) Lgh from Dim_company_se;

--Dot type=3 source=1 select distinct site_id area_id,site_name area_name,length (site_name) Lgh from Dim_company_se; Begin-Replaces outermost loop sql: Large area, province, center, Dot 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,lengt H (province_name) Lgh from Dim_company_se UNION ALL SELECT DISTINCT 2 type, center_id Area_id,site_att area_name,le Ngth (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_s  (e) Loop--judging the number of loops based on the name length for the I in 1..y1.lgh loop--data warehousing 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 C Onnect 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

Modify:

--1. Keyword comes from its own name--Clears table data TRUNCATE table Dim_se_search_key;
--Query data SELECT * FROM Dim_se_search_key where id = ' 999981 '; --da qu type=0 source=1 select distinct domain_id area_id,domain_name area_name,length (domain_name) Lgh from Dim_company_se
; --Province type=1 source=1 select distinct province_id area_id,province_name area_name,length (province_name) Lgh from dim_
Company_se;
--Center type=2 source=1 SELECT distinct center_id area_id,site_att area_name,length (Site_att) Lgh from Dim_company_se; --Dot type=3 source=1 select distinct site_id area_id,site_name area_name,length (site_name) Lgh from Dim_company_se where C

Enter_type= ' dot '; Begin-Replaces outermost loop sql: Large area, province, center, Dot 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,lengt H (province_name) Lgh from Dim_company_se UNION ALL SELECT DISTINCT 2 type, center_id Area_id,site_att area_name,le Ngth (site_atT) Lgh from dim_company_se where center_type = ' Center ' union ALL SELECT DISTINCT 3 type, site_id area_id,site_name ar Ea_name,length (site_name) Lgh from dim_company_se where center_type = ' dot ') loop--to determine the number of loops based on the name length for I in 1. Y1.lgh Loop--data warehousing 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_sub STR (X.area_name, '. { 1, ' | | i| | '} ', y.lv,1) key, Sysdate update_date from X, (select level LV from dual C Onnect 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. The keyword is derived from the 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_i D 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= ' center ' 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= ' outlets ';

Commit

--3. Keywords come from full spell, simple spelling select * from Dim_se_search_key; --Full INSERT INTO Dim_se_search_key select DISTINCT domain_id id,domain_name name,0 type,2. Gethzfullpy (domain_name) key,sysdate update_date from dim_company_se Union SELECT distinct PROVINCE_ID name,1 type,2 source,hzpy. Gethzfullpy (province_name) key,sysdate update_date from dim_company_se Union SELECT distinct center_id 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 = ' dot ';

Commit --Simple spell INSERT INTO Dim_se_search_key SELECT DISTINCT domain_id id,domain_name name,0 type,2. 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 e,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 so Urce,hzpy.
Hzpycap (site_name) key,sysdate update_date from dim_company_se where center_type = ' dot ';

 Commit