Oracle's String splitting

At work, it is found that Oracle does not provide a string cutting function, find a better way to cut on the internet, record:

1. New data type

CREATE OR REPLACE TYPE str_split as TABLE of VARCHAR2 (500);

2. New Cut function

CREATE OR REPLACE FUNCTIONSplitstr (p_stringinch VARCHAR2, P_delimiterinch VARCHAR2)  RETURNstr_split pipelined isV_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); IFV_index= 0  Then  PIPEROW (SUBSTR (p_string, V_start)); V_start:=V_length+ 1; ELSE  PIPEROW (SUBSTR (p_string, V_start, V_index-V_start)); V_start:=V_index+ 1; END IF; ENDLOOP; RETURN; ENDSPLITSTR;

3. Conduct the test

Select *  from Table (Splitstr ('hello,cnblogs! ',', '

4. Convert Rows to columns display

SELECTa.column_value V1, B.column_value V2 from(SELECT *           from(SELECTROWNUM RN, T.*                   from TABLE(Splitstr ('hello,cnblogs!',',')) (T)) A, (SELECT *           from(SELECTROWNUM RN, T.*                   from TABLE(Splitstr ('hello,cnblogs!',',')) ( T)) BWHEREA.rn= 1    andB.rn= 2;

Test results to be supplemented

Oracle's String splitting