CODEWITHSUNDEEP
sqloracle-database
B-Boy
B-Boy

Reputation: 37

How can I join two tables on a field that has an extra character on one side?

I have two tables and need to left join them. The tables need to be joined on OrderNum, however one of the tables stores the OrderNum with a U or a - in the order number, otherwise they do match. The OrderNum example value looks like this: 44440011 in the Orders table and in the other table (Employee) it is stored as: U444-44-0011 or 4444-0011. They both relate to the same Order but it is stored differently in the Employee table because it comes from another system.

I have tried using subtring on the side that has the 'U' at the beginning of the OrderNum, but im not sure how to write this when there are more than one condition i.e. 'U' or '-'.

Select *
from Orders o
Left Join Employee E on o.OrderNum = E. OrderNum

I need the result set to show all the data as a one to many relationship with all fields from Orders table and Employee table

Upvotes: 0

Views: 110

Answers (1)

Popeye
Popeye

Reputation: 35910

You can use the REPLACE to achieve it:

SELECT
    *
FROM
    ORDERS O
    LEFT JOIN EMPLOYEE E 
ON O.ORDERNUM = REPLACE(REPLACE(E.ORDERNUM, 'U', ''), '-', '');

If datatype does not match then you can use to_number on it.

Cheers!!

Upvotes: 3

Related Questions