oracle中split成数组

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN ty_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
str_split ty_str_split := ty_str_split ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);

WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);

IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
str_split.EXTEND;
str_split (str_split.COUNT) := str;

IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
str_split.EXTEND;
str_split (str_split.COUNT) := str;
END IF;
END LOOP;

RETURN str_split;
END fn_split;

 

测试：
SELECT * FROM TABLE (CAST (fn_split ('1;;12;;123;;1234;;12345', ';;') AS ty_str_split));

结果：

1
12
123
1234
12345

 

如果我们查寻条件是这样：100~200-100-200-200~300-200-300',

                      它表示，在100到200之间的,100<x<200

                                 在200到300之间的,200<x<300

如果我们想进行 行转列，可以使入如下语句

select max(case subject when 0 then rv else '0' end) as rangeName ,max(case subject when 1 then rv else '0' end) as from_v,max(case subject when 2 then rv else '0' end) as to_v
from
(
select Column_value as rv ,trunc((rownum-1)/3) as name,MOD(rownum-1,3) as subject from(
SELECT * FROM TABLE (CAST (fn_split ('100~200-100-200-200~300-200-300', '-') AS ty_str_split)))
) group by name

结果是这样的：

100~200 100 200
200~300 200 300