oracle 字符串切割成结果集方法
oracle字符串切割几种方式
- 方法一：
SELECT COLUMN_VALUE FROM TABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));
- 方法二：
select regexp_substr('1,2,3,4,5','[^,]+',1,rownum)
from dual
connect by rownum<=length('1,2,3,4,5')-length(replace('1,2,3,4,5',','))+1
- 方法三：使用自定义函数+table()实现字符串切割
CREATE OR REPLACE TYPE Tbl_StrSplit IS TABLE OF VARCHAR2 (32767);
--实现字符串的切割成数据表 Function Fn_Split( P_String In Clob, P_Delimiter In Varchar2 ) Return Tbl_StrSplit; /*本函数实现字符串的切割操作，通过table()方式返回数据集*/ Function Fn_Split(P_String In Clob, P_Delimiter In Varchar2) Return Tbl_StrSplit Is L_j Pls_Integer := 0; L_i Pls_Integer := 1; L_StringLength Pls_Integer := 0; L_DelimiterLength Pls_Integer := 0; L_CutString Clob := ''; Tbl_SplitItem Tbl_StrSplit := Tbl_StrSplit (); Begin L_StringLength := Length (P_String); L_DelimiterLength := Length (P_Delimiter); While L_j < L_StringLength Loop L_j := Instr (P_String, P_Delimiter, L_i); If L_j = 0 Then L_j := L_StringLength; L_CutString := SUBSTR (P_String, L_i); Tbl_SplitItem.EXTEND; Tbl_SplitItem (Tbl_SplitItem.COUNT) := L_CutString; If L_i >= L_StringLength Then Exit; End If; Else L_CutString := SUBSTR (P_String, L_i, L_j - L_i); L_i := L_j + L_DelimiterLength; Tbl_SplitItem.EXTEND; Tbl_SplitItem (Tbl_SplitItem.COUNT) := L_CutString; End If; End Loop; Return Tbl_SplitItem; End Fn_Split;
总结：(1)Table函数将数组里的内容通过SQL语句查询出来；
(2)ODCIVARCHAR2LIST 在9I 及以上版本中均可使用。在9I中可通过创建TYPE，10G及以上直接使用SYS.ODCIVARCHAR2LIST；
(3)ODCIVARCHAR2LIST 适用于字符集，不适用单个字符串，如果是单个字符串，可以通过参考2中(如下)方法实现。
- 方法四使用正则表达式
Declare
WITH test AS (
SELECT '1,2,3,4,5,10,11,112,455,45,5,52,5,5,5,5,5,5,5,5,5,,5,4,87,8,56,78,8,,,5' AS STR FROM DUAL
)
SELECT DISTINCT REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL)
FROM test
CONNECT BY ROWNUM <= 1000005;
该种正则表达式切割方法不够灵活，且随着level数值变大，效率不断下降。
- 方法五： 使用connect by
WITH T AS (
SELECT '1,2,3,4,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20' AS STR FROM DUAL
)
SELECT STR1
FROM
(
SELECT DISTINCT
SUBSTR(T.CA,INSTR(T.CA, ',', 1, C.LV) + 1,
INSTR(T.CA, ',', 1, C.LV + 1) -(INSTR(T.CA, ',', 1, C.LV) + 1)) AS STR1
FROM (SELECT ',' || STR || ',' AS CA,LENGTH(STR || ',') -NVL(LENGTH(REPLACE(STR, ',')), 0) AS CNT FROM T) T,
(
SELECT LEVEL LV
FROM DUAL
CONNECT BY LEVEL <= 1000000
) C
WHERE C.LV <= T.CNT
ORDER BY STR1
);
该种方法的level的数值也不能太大，否则会提示no enough memory for connect by operation
- 方法六：使用oracle的自定义type类型+管道函数table()实现带有自然数索引的分割函数生成结果集
第一步先创建自定义的type类型"OBJTYPE_VAR"
CREATE OR REPLACE TYPE "OBJTYPE_VAR" FORCE AS OBJECT
(
serialNo NUMBER,
field0 VARCHAR2(1000),
field1 VARCHAR2(1000)
)
第二步定义分割函数
--声明程序包
Create Or Replace Package PKG_Stm_Common Is
--声明<pre code_snippet_id="94384" snippet_file_name="blog_20131203_8_6369260" name="code" class="sql"> Obj_Items类型 的Obj_Items变量
TYPE Obj_Items Is Table Of ObjType_Var;
--实现字符串的切割成数据表，带有自然数索引
Function Fn_SplitWithIndex(
P_String In Clob,
P_Delimiter In Varchar2
)
Return Obj_Items Pipelined;
End PKG_Stm_Common;
--定义程序包体
create or replace package body PKG_Stm_Common is
/*本函数实现字符串的切割操作，通过table()方式返回数据集*/
Function Fn_SplitWithIndex(P_String In Clob, P_Delimiter In Varchar2)
Return Obj_Items Pipelined
Is
L_Index Pls_Integer := 1;
L_j Pls_Integer := 0;
L_i Pls_Integer := 1;
L_StringLength Pls_Integer := 0;
L_DelimiterLength Pls_Integer := 0;
L_CutString Clob := '';
Tbl_SplitItem ObjType_Var := ObjType_Var (null,null,null);
Begin
L_StringLength := Length (P_String);
L_DelimiterLength := Length (P_Delimiter);
While L_j < L_StringLength Loop
L_j := Instr (P_String, P_Delimiter, L_i);
If L_j = 0 Then
L_j := L_StringLength;
L_CutString := SUBSTR (P_String, L_i);
Tbl_SplitItem := ObjType_Var(
L_Index,
L_CutString,
null
);
Pipe Row(Tbl_SplitItem);
If L_i >= L_StringLength Then
Exit;
End If;
Else
L_CutString := SUBSTR (P_String, L_i, L_j - L_i);
L_i := L_j + L_DelimiterLength;
Tbl_SplitItem := ObjType_Var(
L_Index,
L_CutString,
null
);
Pipe Row(Tbl_SplitItem);
End If;
L_Index := L_Index + 1;
End Loop;
Return;
End Fn_SplitWithIndex;
End PKG_Stm_Common;
调用方法：
select *
from table(PKG_Stm_Common.Fn_Split('语文,数学,英语,物理,化学',','))
查询出来的结果
SERIALNO FIELD0 FIELD1 1 1 语文 2 2 数学 3 3 英语 4 4 物理 5 5 化学
wala-wo
