sql分割字符串 oracle,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;