looking for function Split string Oracle -

i looking function in oracle

for example

select xxx('orange,apple,banana,strawberry,blueberry,tomato', ',' 3) dual; 

then shows

orange,apple,banana, 

it counts ',' 3

and

select xxx('orange,apple,banana,strawberry,blueberry,tomato', ',' ,2,3) dual; 

shows

banana,strawberry,blueberry, 

i hope makes sense

than much

there isn't single built-in function quite that. close regular expressions. create own function identify starting , ending positions of segment want, based on commas:

create function foo(p_str varchar2, p_delimiter varchar2,   p_occurrences number, p_position number default 0) return varchar2   l_start pls_integer;   l_end pls_integer;   l_length pls_integer;   l_result varchar2(4000); begin   l_start := case when p_position = 0 1     else instr(p_str, p_delimiter, 1, p_position) + 1 end;   l_end := instr(p_str, p_delimiter, l_start, p_occurrences);   l_length := case when l_end = 0 length(p_str) - l_start + 1   else l_end - l_start end;    l_result := substr(p_str, l_start, l_length);    return l_result; end; / 

then, swapping around arguments example, when supply both (just allow default value position work this):

select foo('orange,apple,banana,strawberry,blueberry,tomato', ',', 1) dual;  orange  select foo('orange,apple,banana,strawberry,blueberry,tomato', ',', 3) dual;  orange,apple,banana  select foo('orange,apple,banana,strawberry,blueberry,tomato', ',', 3, 2) dual;  banana,strawberry,blueberry  select foo('orange,apple,banana,strawberry,blueberry,tomato', ',', 3, 5) dual;  tomato 

if ask more occurrences there returns many exist (i.e. 'tomato' in last call, despite asking 3 elements). if starting position higher number of commas returns original string, can modify return null if prefer.

you in plain sql direct substr , instr calls gets bit messy.

another approach tokenise original string (allowing null elements), pick tokens want keep, , stick them together:

select listagg(token, ',') within group (order position) result (   select level position,     regexp_substr('orange,apple,banana,strawberry,blueberry,tomato',       '(.*?)(,|$)', 1, level, null, 1) token   dual   connect level <     regexp_count('orange,apple,banana,strawberry,blueberry,tomato', '(.*?)(,|$)') ) position between 3 , 5;  banana,strawberry,blueberry