Oracle11g
SQL字符串分割函数
CREATE OR REPLACE TYPE str_split IS 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;
使用
SELECT
*
FROM
TABLE (
splitstr ( 'Hello,Cnblogs!', ',' ));