oracle之字符串拆分

            CREATE OR REPLACE TYPE STR_SPLIT AS TABLE OF VARCHAR2(500);

        

            CREATE
            OR
            REPLACE
            FUNCTION
             SPLITSTR(P_STRING
  
            IN
            VARCHAR2
            , 

 P_DELIMITER 
            IN
            VARCHAR2
            ) 

 
            RETURN
             STR_SPLIT 

 PIPELINED 
            IS
             

 V_LENGTH 
            NUMBER := LENGTH(P_STRING); 

 V_START NUMBER :=1; 

 V_INDEX NUMBER; 

BEGINWHILE (V_START <= V_LENGTH) LOOP 

 V_INDEX := INSTR(P_STRING, P_DELIMITER, V_START); 

 IF V_INDEX =0THENPIPE ROW(SUBSTR(P_STRING, V_START)); 

 V_START := V_LENGTH +1; 

 ELSEPIPE ROW(SUBSTR(P_STRING, V_START, V_INDEX - V_START)); 

 V_START := V_INDEX +1; 

 ENDIF; 

 END LOOP; 

 RETURN; 

END SPLITSTR; 

            select
            *
            from
            table(splitstr(‘Hello,Cnblogs!‘,‘,‘)); 

            SELECT
             A.COLUMN_VALUE V1, B.COLUMN_VALUE V2
  
            FROM (SELECT*FROM (SELECT ROWNUM RN, T.*FROMTABLE(SPLITSTR(‘Hello,Cnblogs!‘, ‘,‘)) T)) A,
       (SELECT*FROM (SELECT ROWNUM RN, T.*FROMTABLE(SPLITSTR(‘Hello,Cnblogs!‘, ‘,‘)) T)) B
 WHERE A.RN =1AND B.RN =2;