Oracle 自定义split 函数实例详解
Oracle自定义split函数
Oracle没有提供split函数,但可以自己建立一个函数实现此功能。比如“abcdefg hijkl nmopqr stuvw xyz”,分隔符是空格,但空格个数不定。
源代码:
CREATEORREPLACETYPEty_str_splitISTABLEOFVARCHAR2(4000); CREATEORREPLACEFUNCTIONfn_var_split( p_strINVARCHAR2, p_delimiterINVARCHAR2 ) RETURNty_str_split IS jINT:=0; lenINT:=0; strVARCHAR2(4000); str_splitty_str_split:=ty_str_split(); v_strVARCHAR2(4000):=RTRIM(LTRIM(p_str,p_delimiter),p_delimiter); BEGIN len:=LENGTH(v_str); WHILElen>0 LOOP j:=INSTR(v_str,p_delimiter,1); IFj=0 THEN str:=SUBSTR(v_str,1); len:=0; str_split.EXTEND; str_split(str_split.COUNT):=str; ELSE str:=SUBSTR(v_str,1,j-1); v_str:=LTRIM(LTRIM(v_str,str),p_delimiter); len:=LENGTH(v_str); str_split.EXTEND; str_split(str_split.COUNT):=str; ENDIF; ENDLOOP; RETURNstr_split; ENDfn_var_split; /
测试:
结果:
1 12 123 1234 12345
DECLARE CURSORc IS SELECT*FROMTABLE(CAST(fn_var_split(';1;12;;123;;;1234;;;;12345;',';')ASty_str_split)); rc%ROWTYPE; BEGIN OPENc; LOOP FETCHcINTOr; EXITWHENc%NOTFOUND; DBMS_OUTPUT.put_line(r.column_value); ENDLOOP; CLOSEc; END; /
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!