How to join two tables based on substring values of fields?

Question

I am having problem with sql. I want to join two tables, employee and class instructor. Condition is that employee is having unid column like 'u0871457' where as class instructor is having EmplId as '00871457'.

I just want to replace the first character of EmplId to 'u' to join to match the string coming from unid . How can I do that? I have tried this so far :

select e.name, i.name
from  Employee e
inner join Instructor i on SUBSTR(e.id,1, LENGTH(e.id )) = SUBSTR(i.id,1, LENGTH(i.id ))

but this is resulting into a blank resultset.

Any help will be appreciated. Thanks for your time!

Answer 1

So many ways to do this. It would be a good idea to look at the explain plan for various ways before committing to a particular method. For example, if there is a function-based index on EMPLOYEE such as SUBSTR(id, 2, LENGTH(id) - 1) then you'll want to use that in your query:

SELECT e.name, i.name
  FROM employee e INNER JOIN instructor i
    ON SUBSTR(e.id, 2, LENGTH(e.id) - 1) = SUBSTR(i.id, 2, LENGTH(i.id) - 1);

Another question is if the values in the id column are always the same length in EMPLOYEE and INSTRUCTOR. What if they are of differing lengths? Maybe one has more padding than another. Also, will they always be digits apart from a leading u? If so, then it might be worthwhile to try a safe TO_NUMBER() conversion:

SELECT e.name, i.name
  FROM employee e INNER JOIN instructor i
    ON TO_NUMBER(REGEXP_SUBSTR(e.id, '\d+$')) = TO_NUMBER(REGEXP_SUBSTR(i.id, '\d+$'));

One other thing you may want to consider, however -- is there a reason for the leading u in the EMPLOYEE id column? Can there be other leading characters? Does the leading u stand for something (violating first normal form, but that happens)?