How to split strings in oracle

To split strings in oracle, first create a function to obtain the number of characters to be split. Www.2cto.com create or replace function Get_StrArrayLength (av_str varchar2, -- the string to be split av_split varchar2 -- separator) return number is lv_str varchar2 (1000); lv_length number; begin lv_str: = ltrim (rtrim (av_str); lv_length: = 0; while instr (lv_str, av_split) <> 0 loop lv_length: = lv_length + 1; lv_str: = substr (lv_str, instr (lv_str, av_split) + length (av_split), length (lv_str); end loop; lv_length: = lv_length + 1; ret Urn lv_length; end Get_StrArrayLength; then, based on the input index, extract the separated character www.2cto.com create or replace function Get_StrArrayStrOfIndex (av_str varchar2, -- the string av_split varchar2 to be split, -- separator av_index number -- Take the nth element) return varchar2 is lv_str varchar2 (1024); lv_strOfIndex varchar2 (1024); lv_length number; begin lv_str: = ltrim (rtrim (av_str); lv_str: = concat (lv_str, av_split); lv_length: = av_index; if lv_length = 0 Then lv_strOfIndex: = substr (lv_str, 1, instr (lv_str, av_split)-length (av_split); else lv_length: = av_index + 1; lv_strOfIndex: = substr (lv_str, instr (lv_str, av_split, 1, av_index) + length (av_split), instr (lv_str, av_split, 1, lv_length)-instr (lv_str, av_split, 1, av_index) -length (av_split); end if; return lv_strOfIndex; end Get_StrArrayStrOfIndex; finally, call these two functions to use the split string like an array in SQL.