CODEWITHSUNDEEP
sqloracle-databasejoin
user3182246
user3182246

Reputation: 49

SQL (Oracle) Joins when column results are slightly different

I need to join 2 tables on a similar column where the data is slightly different between columns. An example:

Table1

Key_ID
______

12345678
12345679
12345671
12345672

Table2

Doc_Num
_______

Z12345678
Z12345679
Z12345671
Z12345672

These are the only columns that are relatable between the 2 tables. I need to join them in order to return data from different columns from Table 2. For example,

SELECT DocTitle, DocDate
FROM Table2
RIGHT JOIN Table1
ON Table2.Doc_Num = Table1.Key_ID

So my main question is how do I join the two tables when there's a "Z" in front of each of the data points I'm trying to join?

Please forgive my ignorance...

Upvotes: 0

Views: 63

Answers (2)

San
San

Reputation: 4538

SELECT DocTitle, DocDate
FROM Table2
RIGHT JOIN Table1
ON Table2.Doc_Num = 'Z' || Table1.Key_ID

or

SELECT DocTitle, DocDate
FROM Table2
RIGHT JOIN Table1
ON substr(Table2.Doc_Num, 2) = Table1.Key_ID

both will work

Upvotes: 1

user330315
user330315

Reputation:

Something like:

select ...
from table1 t1
 right join table2 t2 on t1.key_id = to_number(substr(t2.doc_num,2));

Upvotes: 2

Related Questions