oracle strsplit函数,oracle splitstr 函数

splitstr函数

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;

使用splitstr函数

SELECT COLUMN_VALUE FROM TABLE(SPLITSTR('1,9', ',')) ;

假如表A的一列数据有A1,A2等分隔符分开 此时需要对表A的这个字段进行统计

--id中的值可能有1,2 1 2 等

SELECT

R.NAME ,

SUM(DECODE(E.STATUS,1,1,0)) AS 状态为1的总数,

SUM(DECODE(E.STATUS,2,1,0)) AS 状态为2的总数

FROM A E, 字典表 R

WHERE EXISTS (SELECT COLUMN_VALUE

FROM TABLE(SPLITSTR(E.id, ',')) it

WHERE it.column_value = r.id)

GROUP BY R.NAME;