oracle字符串分割函数代码,PLSQL代码库,德仔网

代码语言

代码分类

【PLSQL】 oracle字符串分割函数

作者:Dezai.CN / 发布于2012/9/19/ 1811

oracle字符串分割函数

<div>CREATE OR REPLACE TYPE mytable AS TABLE OF varchar2(100)</div>
<div></div>
<div>CREATE OR REPLACE FUNCTION SPLIT(SRC VARCHAR2, DELIMITER VARCHAR2)
 RETURN MYTABLE IS
 PSRC VARCHAR2(500);
 A MYTABLE := MYTABLE();
 I NUMBER := 1; -- 
 J NUMBER := 1;
BEGIN
 PSRC := RTRIM(LTRIM(SRC, DELIMITER), DELIMITER);
 LOOP
 I := INSTR(PSRC, DELIMITER, J);
 --Dbms_Output.put_line(i); 
 IF I > 0 THEN
 A.EXTEND;
 A(A.COUNT) := TRIM(SUBSTR(PSRC, J, I - J));
 J := I + 1;
 --Dbms_Output.put_line(a(a.Count-1)); 
 END IF;
 EXIT WHEN I = 0;
 END LOOP;
 IF J < LENGTH(PSRC) THEN
 A.EXTEND;
 A(A.COUNT) := TRIM(SUBSTR(PSRC, J, LENGTH(PSRC) + 1 - J));
 END IF;
 RETURN A;
END;</div>
<div>CREATE OR REPLACE FUNCTION SPLIT(SRC VARCHAR2, DELIMITER VARCHAR2) RETURN MYTABLE IS
PSRC VARCHAR2(500); A MYTABLE := MYTABLE(); I NUMBER := 1; --
J NUMBER := 1;
BEGIN
PSRC := RTRIM(LTRIM(SRC, DELIMITER), DELIMITER); LOOP I := INSTR(PSRC, DELIMITER, J);
--Dbms_Output.put_line(i);
IF I > 0 THEN A.EXTEND; A(A.COUNT) := TRIM(SUBSTR(PSRC, J, I - J)); J := I + 1;
--Dbms_Output.put_line(a(a.Count-1));
END IF; EXIT WHEN I = 0;
END LOOP; IF J < LENGTH(PSRC) THEN A.EXTEND; A(A.COUNT) := TRIM(SUBSTR(PSRC, J, LENGTH(PSRC) + 1 - J));
END IF; RETURN A;
END;
/</div>
<div>数组作为SELECT IN的查询条件</div>
SELECT * FROM STUDENT WHERE ID IN (SELECT * FROM TABLE(CAST(SPLIT('001,002', ',') AS
MYTABLE))); SELECT * FROM STUDENT WHERE ID IN (SELECT * FROM TABLE(CAST(SPLIT('001,002', ',') AS
MYTABLE)));</div>
SELECT * FROM STUDENT WHERE ID IN (SELECT ID FROM STUDENT WHERE ID = '001' UNION SELECT * FROM TABLE(CAST(SPLIT('001,002', ',') AS
MYTABLE))</div>
<div>
</div>

试试其它关键字

　字符串分割　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)