oracle字符串分隔

一、场景，如：86134;86139;86158;

 

分隔成：

             86134

 

             86139

             86158

 

 

 

解决方案:

 

 

1.     CREATE OR REPLACE TYPE split_type IS TABLE OF VARCHAR2 (4000);  

    

 

     2、

 CREATE OR REPLACE FUNCTION split (
	    p_str       IN VARCHAR2,
	    p_delimiter IN VARCHAR2 default(',') --分隔符，默认逗号
	)
	    RETURN split_type
	IS
	    j INT := 0;
	    i INT := 1;
	    len INT := 0;
	    len1 INT := 0;
	    str VARCHAR2 (4000);
	    my_split split_type := split_type ();
	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);
	             my_split.EXTEND;
	             my_split (my_split.COUNT) := str;
	 
	            IF i >= len
	            THEN
	                EXIT;
	            END IF;
	        ELSE
	            str := SUBSTR (p_str, i, j - i);
	            i := j + len1;
	            my_split.EXTEND;
	            my_split (my_split.COUNT) := str;
	        END IF;
	    END LOOP;
	 
	    RETURN my_split;
	END split;
	/

 

3、测试

 

  select * from table (split('86134;86139;86158;',';'));

 

4、结果

86134
86139
86158