PLSQL 实现split
来源:互联网 发布:数据结构算法题 编辑:程序博客网 时间:2024/06/05 05:21
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;
0 0
- PLSQL 实现split
- PLSQL之Split截取字符串
- split实现
- plsql 类似Java中的split方法 字符串分割
- ORCALE实现split
- SQL实现Split 函数
- Oracle 实现split函数
- Sql实现Split
- oracle实现split函数
- SQL split 功能实现
- Mysql实现split函数
- Mysql实现split函数
- SQL Split实现
- oracle 实现 split
- c++实现split
- c++实现split函数
- split c 实现
- C++实现split函数
- osg中的矩阵计算
- javax.comm初步探索
- cookie存取
- Tomcat中server.xml文件详细配置介绍
- 【Leet Code】31. Next Permutation---Medium
- PLSQL 实现split
- Yii2自定义Widget,百度编辑器
- 数据库连接池原理
- MAKEFILE
- unary_function跟binary_function详解
- MongoDB、HandlerSocket和MySQL性能测试及其结果分析
- lisp活着
- web.xml详细介绍
- Android 图片和文字布局,RadioButton有些地方比GridView好用些吧