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 化学