SQL & PL/SQL

I'm trying to create a function to simply extract positioned text from a string, ie I want the text in position two from string X with a pattern of ' - '. I want to create a function so it is simpler for the developers but regexp_substr doesn't seem to like it.


So I want a function like below to be executed, select text_extractor('ABC - DEF - HIJ',' - ',2) from dual; which would return DEF;

But regexp_substr doesn't like matching on ' - '. If I tell it to give me position 2, I get '-', and if I tell it position 3, I get DEF. It seems to work fine if the '-' is substritued with a ':'

ie

select regexp_substr('ABC - DEF - HIJ','[^ - ]+',1,2) from dual;
results
-

select regexp_substr('ABC - DEF - HIJ','[^ - ]+',1,3) from dual;
results
DEF


select regexp_substr('ABC : DEF : HIJ','[^ : ]+',1,2) from dual;
results
DEF

I would further like to make a function to wrap it in but it didn't work at all.

The following examples return nothing.

create or replace function text_extractor (p_text varchar2, p_delimiter varchar2, p_position number)
return varchar2
is
begin
return (regexp_substr(p_text,'^p_delimiter]+', 1,p_position));
end;


I've also tried


create or replace function text_extractor (p_text varchar2, p_delimiter varchar2, p_position number)
return varchar2
is
v_search_expression varchar2(2000);
begin
v_search_expression := '''[^'||p_delimiter||']+''';
return (regexp_substr(p_text,v_search_expression),1,p_position);
end;

But I get nothing. Any ideas?