I am looking for a function in Oracle like this
for example
select xxx('orange,apple,banana,strawberry,Blueberry,tomato', ',' 3) from dual;
THEN shows
orange,apple,banana,
It counts ',' to 3
and
select xxx('orange,apple,banana,strawberry,Blueberry,tomato', ',' ,2,3) from dual;
shows
banana,strawberry,Blueberry,
I hope this makes sense
Than you so much
There isn't a single built-in function that will do quite that. You could get close with regular expressions. But you could also create your own function to identify the starting and ending positions of the segment you want, based on the commas:
create function foo(p_str varchar2, p_delimiter varchar2,
p_occurrences number, p_position number default 0)
return varchar2 is
l_start pls_integer;
l_end pls_integer;
l_length pls_integer;
l_result varchar2(4000);
begin
l_start := case when p_position = 0 then 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 then 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 the arguments from your example, when you supply both (just to allow the default value for position to work like this):
select foo('orange,apple,banana,strawberry,Blueberry,tomato', ',', 1) from dual;
orange
select foo('orange,apple,banana,strawberry,Blueberry,tomato', ',', 3) from dual;
orange,apple,banana
select foo('orange,apple,banana,strawberry,Blueberry,tomato', ',', 3, 2) from dual;
banana,strawberry,Blueberry
select foo('orange,apple,banana,strawberry,Blueberry,tomato', ',', 3, 5) from dual;
tomato
If you ask for more occurrences than there are then it only returns as many as exist (i.e. just 'tomato' in the last call, despite asking for 3 elements). If your starting position is higher than the number of commas it returns the original string, but you can modify it to return null if you prefer.
You could do this in plain SQL with direct substr and instr calls but it gets a bit messy.
Another approach is to tokenise the original string (allowing for null elements), pick the tokens you want to keep, and stick them back together:
select listagg(token, ',') within group (order by position) as result
from (
select level as position,
regexp_substr('orange,apple,banana,strawberry,Blueberry,tomato',
'(.*?)(,|$)', 1, level, null, 1) as token
from dual
connect by level <
regexp_count('orange,apple,banana,strawberry,Blueberry,tomato', '(.*?)(,|$)')
)
where position between 3 and 5;
banana,strawberry,Blueberry
