CODEWITHSUNDEEP
oracle-database
Mr.Doge
Mr.Doge

Reputation: 57

how do I split a numeric column into two separate columns in Oracle SQL

I am trying to join two tables together but the key in one table is formated like 'xxxxxx' and the key I am trying to join together in the second table is formated like '2222xxxxxx'. Is there a way I can either split the second column into two different columns to make the join, or join on only the last 6 numbers of column 2?

Notes: values are numeric. the '2222' is always the same 4 numbers.

Upvotes: 1

Views: 2254

Answers (2)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210982

I would suggest a slightly different approach:

If your column has varchar/char data type:

drop table tab1;

drop table tab2;

create table tab1(col char(30));

create table tab2(col char(30));

insert into tab1
select lpad(to_char(level), 6, '0') as val
from dual connect by level <=100 order by level;

insert into tab2
select '2222' || lpad(to_char(level), 6, '0') as val
from dual connect by level <=100 order by level;

create unique index ux_tab1 on tab1(col);

create unique index ux_tab2 on tab2(col);

select tab1.col, tab2.col
from tab1
join tab2
  on '2222' || tab1.col = tab2.col;

-- check execution plan
explain plan for
select tab1.col, tab2.col
from tab1
join tab2
  on '2222' || tab1.col = tab2.col;

select * from table(dbms_xplan.display);

If your column has number data type:

drop table tab1;

drop table tab2;

create table tab1(col number);

create table tab2(col number);

insert into tab1
select 100000 + level as val
from dual connect by level <=100 order by level;

insert into tab2
select 2222100000 + level as val
from dual connect by level <=100 order by level;

create unique index ux_tab1 on tab1(col);

create unique index ux_tab2 on tab2(col);

select tab1.col, tab2.col
from tab1
join tab2
  on 2222000000 + tab1.col = tab2.col;


-- check execution plan
explain plan for
select tab1.col, tab2.col
from tab1
join tab2
  on 2222000000 + tab1.col = tab2.col;

select * from table(dbms_xplan.display);

Using this approach Oracle will be able to use indexes for joining !

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23381

Try this way:

select * 
  from table1 a 
         INNER JOIN table2 b
                 on a.fieldname = substr(b.fieldname, -6)

Here is the documentation for the SUBSTR Function in Oracle.

Know that this is a very bad design for your second table you should consider in separating this values in different columns.

EDIT

This comment from @Boneist "if there were 7 numbers after the first 4, not just 6?"

To fix this you should use: substr(b.fieldname, 5)

To separate that column on a select statement either you create a new column and update it values with the given substr or just add it in the select command.

On the select command:

select fielda, fieldb, 
       substr(b.fieldname, 1, 4) firstPartOfField,
       substr(b.fieldname, 5) secondPartOfField
  from tableb

To create another column it would be

alter table tableb add column newField number(6);
update tableb 
   set oldField = substr(b.fieldname,1,4),
       newField = substr(b.fieldname,5);

Upvotes: 3

Related Questions