## Question and Answer

## You Asked

Hi,

I have column having data like below.

101010

10203040

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.

I have column having data like below.

101010

10203040

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

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

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)

## Rating

# Comments

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.

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.

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

Currently it is

substr(x,level*2-1,2)

and the function args are: string, start_position, length

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

Currently it is

substr(x,level*2-1,2)

and the function args are: string, start_position, length

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

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:

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):

I will let you select from TBL after this INSERT to convince yourself that it does what was asked.

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.

Thanks Connor, it worked.

glad to help