oracle string util package

oracle string util package

-- 1. create type as object

create or replace type ty_col01 as object
(
    col01 varchar2(200)
);

-- 2. create type as table

create or replace type ty_tbl01 as table of ty_col01;

-- 3. create package

create or replace package pkg_string as
    /*********************************************
    Description.
        pkg_string sting util
         
    Parameter.
           
    Create.  
        2018.12.28  hsy
   
    Modify.
   
    **********************************************/
    function fn_stringtoarray
    (
        ps_str in varchar2,
        ps_group in integer
    ) return ty_tbl01;

    function fn_stringtoarray
    (
        ps_str in varchar2,
        ps_separ in varchar2
    ) return ty_tbl01;

end;

-- 4. create package body

create or replace package body pkg_string as

    /*********************************************
    Description.
        fn_stringtoarray
        fn_stringtoarray
         
    Parameter.
           
    Create.  
        2018.12.28  hsy
   
    Modify.
   
    **********************************************/

    /*--- 문자열, 자리수로 자르기 ---*/
    function fn_stringtoarray
    (
        ps_str in varchar2,
        ps_group in integer
    ) return ty_tbl01 as
   
        ty_rtn ty_tbl01;
   
    begin
   
        select ty_col01(code_list)
          bulk collect
          into ty_rtn
          from (select substr(ps_str, 1 + ((level - 1) * ps_group), ps_group) as code_list
                  from dual
                connect by level <= (length(ps_str) / ps_group) + 1)
         where code_list is not null;
   
        return ty_rtn;
   
    end;

    /*--- 문자열, 구분자로 자르기 ---*/
    function fn_stringtoarray
    (
        ps_str in varchar2,
        ps_separ in varchar2
    ) return ty_tbl01 as
   
        ty_rtn ty_tbl01;
   
    begin
   
        select ty_col01(code_list)
          bulk collect
          into ty_rtn
          from (select regexp_substr(ps_str, '[^' || '' || ps_separ || '' || ']+', 1, level) as code_list
                  from dual
                connect by instr(ps_str, ps_separ, 1, level - 1) > 0)
         where code_list is not null;
   
        return ty_rtn;
    end;

end;

-- 5. select

select *
  from table(pkg_string.fn_stringtoarray('1234567890', 1));

select *
  from table(pkg_string.fn_stringtoarray('1;2;3;4;5;67890', ';'));