/*Oracle 字符串拆分*/
/*正则表达式方式*/
SELECT REGEXP_SUBSTR ('abc1,cbd2,db3,db5', '[^,]+', 1,rownum)
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH ('abc1,cbd2,db3,db5') - LENGTH (REPLACE ('abc1,cbd2,db3,db5', ',', ''))+1;
/*使用函数*/
--创建类型
CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000)
--创建函数
CREATE OR REPLACE FUNCTION fun_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 fun_splitstr;
instr(s1,s,n) --从第n个字符开始,在s1中查找s的位置
substr(string,v_start,v_length)--在字符串string中,从第v_start个字符开始,截取长度为v_length的字符串