Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Sayyed.

Asked: May 22, 2023 - 2:20 pm UTC

Last updated: May 29, 2023 - 3:39 am UTC

Version: 19c

Viewed 1000+ times

You Asked


I have column having data like below.

2030405060 etc

From each row above I have to break as below to check whether that number exist in a table or not, if yes skip and if no then insert.

E.g. for first row 101010
First I have extract two digit ie 10 and check if exist in a table or not and if not then insert if yes then skip.

Second time i have to take first 4 digital eg 1010 and verify whether exist in a table or not.

Similarly I have to look 101010 last time for first row to verify if it exist or not.

Can you please help how to achieve this. Thanks.

and Connor said...

A lateral clause lets you break up the chunks into rows

SQL> with t as
  2  ( select '2030405060' x from dual union all
  3    select '102030' x from dual
  4  )
  5  select x, piece
  6  from t, lateral(select substr(x,level*2-1,2) piece
  7   from dual
  8  connect by level <= length(x)/2
  9  );

X          PIECE
---------- --------
2030405060 20
2030405060 30
2030405060 40
2030405060 50
2030405060 60
102030     10
102030     20
102030     30

Once you have that, its easy, you just add an EXISTS clause, eg

with t as 
( select '2030405060' x from dual union all
  select '102030' x from dual
), t1 as
select x, piece 
from t, lateral(select substr(x,level*2-1,2) piece 
 from dual
connect by level <= length(x)/2
select *
from t1
where not exists ( 
  select 1
  from  other_table
 where col = t1.piece)


  (3 ratings)


A reader, May 23, 2023 - 6:04 am UTC

Thank you so much Connor for your help.
I check the code you provided where it gives me 2 digits every time.

However when i will check second time i need to extract 4 digit and again if will go further (if it 8 digit number) then third time i will extract 6 digit. Please find below example for 10 digit number.

1 first
2030405060 20
2 time
2030405060 2030
3 time
2030405060 203040
4 time
2030405060 20304050

if total digits are 10 ( in above example) i will check 4 times similarly
if total digit are 6 then i will check 2 times e,g 101010
1st time
101010 10
101010 1010 thats it, mean whenever i will check i will go till length -2

Also just for information this column always contain digits in even
so length always like 2,4,6,8,10,12 etc.

Thanks again for your immediate help and support.
Connor McDonald
May 24, 2023 - 4:33 am UTC

Well...that is just going to be a slight change to you substring

Currently it is


and the function args are: string, start_position, length

So I'm sure you can make that easy change.

breaking down prefixes using recursive query

mathguy, May 24, 2023 - 2:52 am UTC

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.


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


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)
    r (z) as (
      select  n
        from  new_values
      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.

SQl Help

A reader, May 24, 2023 - 10:30 am UTC

Thanks Connor, it worked.
Connor McDonald
May 29, 2023 - 3:39 am UTC

glad to help

More to Explore


The Oracle documentation contains a complete SQL reference.