sql - How to get specific value from filename by substr-instr

Question

I have 2 file which names like abc_primary.log and abc_secondary.log and i need the output like primary and secondary only using substr and instr.

select SUBSTR(name1,(LENGTH(name1)-INSTR(REVERSE(name1), '.', 1)+2)) 
from 
(
  select 
    'abc_primary.log' as name1 ,
    'abc_secondary.log' as name2 
  from dual
);

here i can get the output is 'log' which is the right-side part of the file. But i need the output will be only either primary or secondary. Can anyone here help me to get the value. Thanks

标签： sqloracle

Answer 1

如果你只想使用substr然后instr你可以这样写：

select substr(substr(name1, 1, instr(name1, '.') - 1), instr(name1, '_') + 1) n1, 
       substr(substr(name2, 1, instr(name2, '.') - 1), instr(name2, '_') + 1) n2 
  from (
    select 'abc_primary.log' as name1, 'abc_secondary.log' as name2 
    from dual);

结果：

N1      N2
------- ---------
primary secondary