oracle使用substr instr实现类似于java substring的功能

select cast(substr(a.str1,
                   instr(a.str1, ';', 1, '27') + 1,
                   (instr(a.str1, ';', 1, '28') -
                   instr(a.str1, ';', 1, '27') - 1)) as varchar2(10)) as value1
  from table1 a
 where (length(a.str1) - length(replace(a.str1, ';', ''))) = 29
   and cast(substr(a.str1,
                   instr(a.str1, ';', 1, '27') + 1,
                   (instr(a.str1, ';', 1, '28') -
                   instr(a.str1, ';', 1, '27') - 1)) as varchar2(10)) is not null;