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