PLSQL 实现split:
FUNCTION
Get_Field_Value
(
in_str IN VARCHAR2 ,
findstr IN VARCHAR2 ,
field IN INTEGER
) RETURN VARCHAR2 AS
tmp_msg VARCHAR2 ( 20 );
-- 取字段 值 ,field start from 0
startidx INTEGER ;
endidx INTEGER ;
BEGIN
startidx : = 1 ;
endidx : = 1 ;
FOR i IN 1 .. field
LOOP
-- INSTR(in_string,'|',tmp_pos);
-- substr(in_string,tmp_pos,tmp_pos1-tmp_pos);
IF i > 1 THEN
startidx : = INSTR(in_str, findstr, startidx);
IF startidx = 0 THEN
RETURN NULL ;
END IF ;
startidx : = startidx + length(findstr);
END IF ;
END LOOP;
endidx : = INSTR(in_str, findstr, startidx);
IF endidx = 0 THEN
endidx : = length(in_str) + 1 ;
END IF ;
RETURN substr(in_str, startidx, endidx - startidx);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL ;
END ;
(
in_str IN VARCHAR2 ,
findstr IN VARCHAR2 ,
field IN INTEGER
) RETURN VARCHAR2 AS
tmp_msg VARCHAR2 ( 20 );
-- 取字段 值 ,field start from 0
startidx INTEGER ;
endidx INTEGER ;
BEGIN
startidx : = 1 ;
endidx : = 1 ;
FOR i IN 1 .. field
LOOP
-- INSTR(in_string,'|',tmp_pos);
-- substr(in_string,tmp_pos,tmp_pos1-tmp_pos);
IF i > 1 THEN
startidx : = INSTR(in_str, findstr, startidx);
IF startidx = 0 THEN
RETURN NULL ;
END IF ;
startidx : = startidx + length(findstr);
END IF ;
END LOOP;
endidx : = INSTR(in_str, findstr, startidx);
IF endidx = 0 THEN
endidx : = length(in_str) + 1 ;
END IF ;
RETURN substr(in_str, startidx, endidx - startidx);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL ;
END ;
例二
TYPE ARRAY
IS
TABLE
OF
VARCHAR2(
255);
FUNCTION SPLIT(P_STR IN VARCHAR2, P_DELIMITER IN VARCHAR2) RETURN ARRAY IS
J INT : = 0;
I INT : = 1;
LEN INT : = 0;
LEN1 INT : = 0;
STR VARCHAR2( 4000);
STR_SPLIT ARRAY : = ARRAY();
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);
STR_SPLIT.EXTEND;
STR_SPLIT(STR_SPLIT. COUNT) : = STR;
IF I >= LEN THEN
EXIT;
END IF;
ELSE
STR : = SUBSTR(P_STR, I, J - I);
I : = J + LEN1;
STR_SPLIT.EXTEND;
STR_SPLIT(STR_SPLIT. COUNT) : = STR;
END IF;
END LOOP;
RETURN STR_SPLIT;
END SPLIT;
FUNCTION SPLIT(P_STR IN VARCHAR2, P_DELIMITER IN VARCHAR2) RETURN ARRAY IS
J INT : = 0;
I INT : = 1;
LEN INT : = 0;
LEN1 INT : = 0;
STR VARCHAR2( 4000);
STR_SPLIT ARRAY : = ARRAY();
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);
STR_SPLIT.EXTEND;
STR_SPLIT(STR_SPLIT. COUNT) : = STR;
IF I >= LEN THEN
EXIT;
END IF;
ELSE
STR : = SUBSTR(P_STR, I, J - I);
I : = J + LEN1;
STR_SPLIT.EXTEND;
STR_SPLIT(STR_SPLIT. COUNT) : = STR;
END IF;
END LOOP;
RETURN STR_SPLIT;
END SPLIT;
调用:
STR_SPLIT := SPLIT('A,B,C,D,E,F', ',');
FOR I IN 1 .. STR_SPLIT.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(I);
END LOOP;