Oracle String Segmentation

Split function One:

String segmentation:

Create or Replace function get_strarraylength
(
Av_str VARCHAR2,--the string to be split
Av_split VARCHAR2--Separator symbol
)
return number
Is
Lv_str VARCHAR2 (1000);
Lv_length number;
Begin
Lv_str:=ltrim (RTrim (AV_STR));
lv_length:=0;
While InStr (Lv_str,av_split) <>0 loop
lv_length:=lv_length+1;
Lv_str:=substr (Lv_str,instr (lv_str,av_split) +length (av_split), Length (LV_STR));
End Loop;
lv_length:=lv_length+1;
return lv_length;
End Get_strarraylength;

String Extraction:

Create or Replace functionGet_strarraystrofindex (Av_strvarchar2,--the string to splitAv_splitvarchar2,--Delimited SymbolsAv_index Number --take the first few elements)return varchar2 isLv_strvarchar2(1024x768); Lv_strofindexvarchar2(1024x768); Lv_length Number;beginLv_str:=LTrim(RTrim(AV_STR)); LV_STR:=concat (Lv_str,av_split); Lv_length:=Av_index; ifLv_length=0  ThenLv_strofindex:=SUBSTR (LV_STR,1, InStr (Lv_str,av_split)-Length (av_split)); Elselv_length:=Av_index+1; Lv_strofindex:=substr (Lv_str,instr (Lv_str,av_split,1, Av_index)+Length (Av_split), InStr (Lv_str,av_split,1, Lv_length)-InStr (Lv_str,av_split,1, Av_index)-Length (av_split)); End if; returnLv_strofindex;EndGet_strarraystrofindex;

Split function Two:

--This function splits the target string with the specified string and returns the result through the table structure. The code is as follows:CREATE OR REPLACETYPE Str_split is TABLE  of VARCHAR2(4000);CREATE OR REPLACE FUNCTIONSplitstr (p_stringinch VARCHAR2, P_delimiterinch VARCHAR2)    RETURNstr_split pipelined asV_length Number:=LENGTH (p_string); V_start Number:= 1; V_index Number;BEGIN     while(V_start<=v_length) LOOP V_index:=INSTR (p_string, P_delimiter, V_start); IFV_index= 0         Then            PIPEROW (SUBSTR (p_string, V_start)); V_start:=V_length+ 1; ELSE            PIPEROW (SUBSTR (p_string, V_start, V_index-V_start)); V_start:=V_index+ 1; END IF; ENDLOOP; RETURN;ENDSPLITSTR;

Case one:

Select Get_strarraylength ('hjjhtscyjd_tb13522018,hjjhtscyjd_2012_tb13632018,hjjhtscyjd_2013_tb13642018, hjjhtscyjd_2014_tb13652018,hjjhtscyjd_2015_tb13662018,hjjhtscyjd_2016_tb13672018,hjjhtscyjd_2017_tb16522018  ',', ' from dual;

Case TWO:

Select Get_strarraystrofindex ('hjjhtscyjd_tb13522018,hjjhtscyjd_2012_tb13632018,hjjhtscyjd_2013_ Tb13642018,hjjhtscyjd_2014_tb13652018,hjjhtscyjd_2015_tb13662018,hjjhtscyjd_2016_tb13672018,hjjhtscyjd_2017_ TB16522018',',',1 from dual;

Case THREE:

Select *  from Table (Splitstr ('HJJHTSCYJD_TB13522018,HJJHTSCYJD_2012_TB13632018,HJJHTSCYJD_2013_TB13642018,HJJHTSCYJD _2014_tb13652018,hjjhtscyjd_2015_tb13662018,hjjhtscyjd_2016_tb13672018,hjjhtscyjd_2017_tb16522018' ,','));

Case FOUR: Summing the total data for each table

SELECT sum("Count") from(Select Count(1) "Count" fromhjjhtscyjd_tb13522018Union  All  Select Count(1) "Count" fromhjjhtscyjd_2012_tb13632018Union  All  Select Count(1) "Count" fromhjjhtscyjd_2013_tb13642018Union  All  Select Count(1) "Count" fromhjjhtscyjd_2014_tb13652018Union  All  Select Count(1) "Count" fromhjjhtscyjd_2015_tb13662018Union  All  Select Count(1) "Count" fromhjjhtscyjd_2016_tb13672018Union  All Select Count(1) "Count" fromHJJHTSCYJD_2017_TB16522018);

Oracle String Segmentation