create table test as
select  to_char(1000+level) STR
from dual
connect by level<=1e6
/
create table prefixes(prefix varchar2(10) primary key,descr varchar2(100));
insert into prefixes
select column_value prefix 
      ,'descr '||column_value descr
from table(ku$_vcnt('1','2','3','4','5','6','7','8','9'
                  ,'10','100','14','145'
                  ,'22','222'
                  ,'30','300','31','311'
                  ,'44','444'
                  ,'55','555','5555'
                  ,'66','666','6666'
                  ,'77','777','700'
                  ,'80','800','801'
                  ,'90','99','993')) t
/

alter table prefixes add len number generated always as (length(prefix))
/
create unique index ix_prefixes on prefixes(len,prefix,descr);
/
with 
  n as (
    select/*+ inline */ (select max(len) from prefixes)-level+1 N
    from dual 
    connect by level<=(select max(len) from prefixes)
 )
select
  str
  ,(select 
       (select p.descr from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n))
    from N
    where (select p.descr from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n)) is not null
      and rownum=1
   )
from test;

alter table prefixes add len number generated always as (length(prefix))
/
create unique index ix_prefixes on prefixes(len,prefix,descr);
/
create or replace function max_prefix_len return int result_cache deterministic as
   res int;
begin
   select max(len) into res from prefixes;
   return res;
end;
/
create or replace function get_prefix(str in varchar2) return rowid result_cache deterministic as
   ml int := max_prefix_len;
   res rowid;
   cursor c is 
     with 
       n as (
       select/*+ inline */ ml-level+1 N
       from dual 
       connect by level<=ml
     )
     select 
       (select p.rowid from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n))
     from N
     where (select p.rowid from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n)) is not null;

begin
   open c;
   fetch c into res;
   close c;
   return res;
end;
/
select
  str,p.*
from test t
    ,prefixes p
where p.rowid=get_prefix(substr(t.str,1,max_prefix_len));