I have strings : 'A-20-1-1', 'A-10-10', 'A-10-11-1'
And result from substringing:
'A-20-1-1', 'A-10-10', 'A-10-11-1'
1 10 11
Code won't works fine:
Select Substr(string, instr(string,'-',1,2)+1, instr(string, '-',1,2)-1)
From dual;
At the beginning I find second '-', than next one if exists. If not I get string length.
create table a(b varchar2(20));
insert into a values('A-20-1-1');
insert into a values('A-10-10');
insert into a values('A-10-11-1');
Select
b,
substr(b,instr(b,'-',1,2)+1,decode(instr(b,'-',1,3),0,length(b)-instr(b,'-',1,2),instr(b,'-',1,3)-instr(b,'-',1,2)-1)) z
from a;
gives us what you need:
A-20-1-1 1
A-10-10 10
A-10-11-1 11
At first I find the second - sign position, and then get a substring of the value from this position to the rest of the value. Then I exclude the part of the string from previous step if the string has the - sign. Like this:
with t(d) as (
select 'A-20-1-1' from dual union all
select 'A-10-10-4' from dual union all
select 'A-10-11-1' from dual
)
select REPLACE(SUBSTR(d, INSTR(d, '-', 1, 2) + 1), SUBSTR(d, INSTR(d, '-', 1, 3))) from t
RES
---
1
10
11
-sign?regexp_substr(string, '[^-]+', 1, 3)fiddle