Oracle数据库字符串按某字段分割（函数）

qq_21339919

已于 2022-07-27 16:34:21 修改

阅读量514

点赞数

文章标签： oracle 数据库 database

于 2021-11-04 13:19:07 首次发布

本文链接：https://blog.csdn.net/qq_21339919/article/details/121140442

版权

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(',1,2,3,4,5,6,7,a',','))