create table force_powers ( 
  id integer generated by default as identity not null primary key, 
  structure varchar2(50) not null, 
  name varchar2(100) not null 
)

insert into force_powers (structure, name) values ( '1', 'Universal' )

insert into force_powers (structure, name) values ( '1.1', 'Telekinesis' )

insert into force_powers (structure, name) values ( '1.1.1', 'Force Push' )

insert into force_powers (structure, name) values ( '1.1.2', 'Force Pull' )

insert into force_powers (structure, name) values ( '1.1.3', 'Force Jump' )

insert into force_powers (structure, name) values ( '2', 'Light' )

insert into force_powers (structure, name) values ( '2.1', 'Mind Trick' )

insert into force_powers (structure, name) values ( '2.1.1', 'Force Persuation' )

insert into force_powers (structure, name) values ( '3', 'Dark' )

insert into force_powers (structure, name) values ( '3.1', 'Force wound' )

insert into force_powers (structure, name) values ( '3.1.1', 'Force grip' )

insert into force_powers (structure, name) values ( '3.1.2', 'Force choke' )

select * from force_powers

create or replace type t_numeric_structure force as object 
( 
  c_level1 number(3,0), 
  c_level2 number(3,0), 
  c_level3 number(3,0), 
 
  constructor function t_numeric_structure( 
    i_struct varchar2 ) 
    return self as result, 
  member function p$_position_for_level( 
    i_string varchar2, 
    i_level positiven ) return pls_integer, 
  member function structure return varchar2 deterministic 
); 

create or replace type body t_numeric_structure as 
 
  constructor function t_numeric_structure( 
    i_struct varchar2 ) 
    return self as result 
  as 
    begin 
      self.c_level1 := p$_position_for_level(i_struct, 1); 
      self.c_level2 := p$_position_for_level(i_struct, 2); 
      self.c_level3 := p$_position_for_level(i_struct, 3); 
      return; 
    end; 
 
  member function p$_position_for_level( 
    i_string in varchar2, 
    i_level in positiven ) return pls_integer 
  as 
    begin 
      return 
        nvl(to_number( 
          regexp_substr(i_string, '[0-9]+', 1, i_level) 
        ),0); 
    end; 
 
  member function structure return varchar2 deterministic 
  as 
    l_result varchar2(50); 
    begin 
      if ( c_level1 > 0 ) then 
        l_result := to_char(c_level1); 
      end if; 
      if ( c_level2 > 0 ) then 
        l_result := l_result || '.' || to_char(c_level2); 
      end if; 
      if ( c_level3 > 0 ) then 
        l_result := l_result || '.' || to_char(c_level3); 
      end if; 
      return l_result; 
    end; 
end; 

alter table force_powers 
  add struct t_numeric_structure

update force_powers 
  set struct = t_numeric_structure(structure)

alter table force_powers 
  drop column structure

create unique index idx_force_powers_uq_struct on 
  force_powers ( substr(struct.structure(),1,12) )

insert into force_powers ( name, struct ) 
select 'some name', t_numeric_structure('4.0.0') 
from dual connect by level <= 10

update force_powers p 
  set struct = t_numeric_structure('4.0.0') 
  where p.struct.structure() like '3%'

update force_powers p 
  set p.struct.c_level1 = 1 
  where p.struct.c_level1 = 2

select 
  p.id, 
  p.name, 
  p.struct.structure() structure 
  from force_powers p 
  where p.struct.structure() = '1.1.1'