1. 新建数据库类型

sql

create or replace type STR_SPLIT as table of varchar2(50);

2. 创建function

sql

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;
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;

3. 测试

sql

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

将行转为列显示：

sql

select a.column_value v1, b.column_value v2
  from (select *
          from (select rownum rn, t.*
                  from table(splitstr('Hello,Cnblogs!', ',')) t)) a,
       (select *
          from (select rownum rn, t.*
                  from table(splitstr('Hello,Cnblogs!', ',')) t)) b
 where a.rn = 1
   and b.rn = 2;