Here's one way to do this. In many problems like this, you can use a hierarchical (CONNECT BY) query, with or without the LATERAL clause, or you can use a recursive query (recursive WITH clause). In most cases the hierarchical query is faster, and perhaps that's the case here too - but it's worth a try. In this problem, in the recursive query you don't join anything, you just apply a simple operation repeatedly to each successive result set; there isn't much work to do, so perhaps this is just as fast as a hierarchical query.
You said your values are numbers, so I will use numeric operations (divide by 100 and truncate) instead of SUBSTR, which is more appropriate for strings. The same solution would work the same if your values were strings, not numbers. In both cases, it's easiest to start from the full value, like 112233, then chop off the last two digits (the second value is 1122) and repeat as needed, rather than starting from the left end.
You also said you need to insert the "new" values into an existing table while ignoring those that are already present. So I will show a complete solution.
As preparation, we have two tables: the NEW_VALUES table with the input numbers, and the "other" table, TBL, which may already have some values in it, and in which we must add the "prefixes" that aren't present already. In TBL we may have all sorts of numbers; we may have NULL, we may have some duplicate values, and we may have numbers with an odd number of digits. (You didn't tell us what else is in that table, so we can't assume anything.) My sample table will have instances of all these "odd" cases.
Preparation:
create table tbl (x number);
insert into tbl
select 30 from dual union all
select 2010 from dual union all
select 20 from dual union all
select 101010 from dual union all
select 30 from dual union all
select 44 from dual union all
select 555555 from dual union all
select null from dual union all
select 354 from dual
;
create table new_values (n number);
insert into new_values
select 101010 from dual union all
select 10203040 from dual union all
select 2030405060 from dual union all
select 80 from dual
;
commit;
Then the INSERT might look like this (where most of the work is done in the SELECT statement; notice the WHERE clause in the subquery in the NOT IN condition):
insert into tbl(x)
with
r (z) as (
select n
from new_values
UNION ALL
select trunc(z/100)
from r
where z > 99
)
select distinct z
from r
where z not in (select x from tbl where x is not null)
;I will let you select from TBL after this INSERT to convince yourself that it does what was asked.