CODEWITHSUNDEEP
sqloracle-database
Inoracle
Inoracle

Reputation: 57

Substring from string oracle

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;

Upvotes: 2

Views: 133

Answers (2)

psmith
psmith

Reputation: 1813

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

Upvotes: 2

neshkeev
neshkeev

Reputation: 6476

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

Upvotes: 1

Related Questions