oracle 用 切割字符串,oracle数据库字符串切割 split

1 新建数据库类型

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

2 创建function

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 测试

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

将行转为列显示：

select a.column_value v1,b.column_value v2 from

(select * from (select rownum rn,t.* from table(splitstr('Hello,!',',')) t)) a,

(select * from (select rownum rn,t.* from table(splitstr('Hello,!',',')) t)) b

where a.rn=1 and b.rn=2