Oracle SQL 字符分割

select get_carton_list_split(',2,4,5,7,9,10,12,13,14') from dual;  

得到如下结果：

2,4~5,7,9~10,12~14

下面是用到的自定义函数，以供参考！

create or replace type tb_strSplit as table of varchar2(4000);

CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN str_split
    PIPELINED
AS
    v_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);


        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + 1;
        END IF;
    END LOOP;


    RETURN;
END splitstr;

create or replace function get_carton_list_split (
carton_list in varchar2
)
return varchar as
v_carton_list_split varchar2(2000);
begin


declare
  value number;
  diff number;
  previousvalue number;
  nextvalue number;
  ret varchar2(2000);
cursor c_split is
  select value,diff,nvl(lag(value)over(order by value),0) previousvalue,nvl(lead(value)over(order by value),0) nextvalue from (
  select to_number(column_value) value,nvl(to_number(column_value)-lag(to_number(column_value)) over (order by 1),0) diff
  from table(splitstr(carton_list,',')) where column_value is not null order by to_number(column_value)) ;
begin
  open c_split;
  loop
     fetch c_split into value,diff,previousvalue,nextvalue;
     exit when c_split%notfound;
     ret:=ret|| (case
     when diff=0 and nvl(nextvalue,0)=0 then to_char(value)
     when diff=0 and nextvalue-value=1 then to_char(value)||'~'
     when diff=0 and nextvalue-value>1 then to_char(value)||','
     when diff>1 and nextvalue-value=1 and value-nvl(previousvalue,0)>1 then to_char(value)||'~'
     when diff>1 and nextvalue-value=1 and value-nvl(previousvalue,0)=1 then ''
     when diff>1 and nextvalue-value>1 and value-nvl(previousvalue,0)>1 then to_char(value)||','
     when diff>1 and nvl(nextvalue,0)=0 then to_char(value)

     when diff>1 and nextvalue-value>1 then ','
     when diff=1 and nextvalue-value>1 then to_char(value)||','
     when diff=1 and nvl(previousvalue,0)=0 then ''
     when diff=1 and nextvalue-value=1 then ''
     when diff=1 and nvl(nextvalue,0)=0 then to_char(value)
     else ',' end);
  end loop;
   v_carton_list_split:=ret;
  close c_split;
end;


return v_carton_list_split;
exception
  when others then
    return null;
end;