boombox2014 boombox2014 - 10 days ago 8
SQL Question

Oracle SQL - Join keys

These are my tables and sample records...




DEPARTMENT.Dept_nbr (VARCHAR)

---------------------

(NULL)

UNK

00309

309

FPLAN.Department (NUMBER)

---------------------

D0309

Scenario:

I want FPLAN.Department (D0309) to be the reference and will display DEPARTMENT.Dept_nbr values.

My code:


select
TRIM(REPLACE(FPL.DEPARTMENT, 'D')) as DEPARTMENT , DEPT.*
from
FPLAN FPL ,
(
select distinct(TRIM(LEADING 0 FROM DEPT_NBR)) DEPT_NBR from DEPARTMENT
) DEPT
WHERE
TRIM(LEADING 0 FROM FPL.DEPARTMENT) = DEPT.DEPT_NBR;


This brings NO RESULT





How does my query result to have this DEPT_NBR:

00309

309

Answer

If your meaningful strings are always in the format 'DXXXX' where XXXX represents a number, you may convert everything to numbers and then compare numeric values:

select Dept_nbr
from (
     select *
     from DEPARTMENT
     where regexp_instr( Dept_nbr,'([^0-9])') = 0
     ) 
       inner join FPLAN
         on ( to_number(trim( 'D' from Department)) = to_number(Dept_nbr) )