CODEWITHSUNDEEP
sqloracle-databasejoinsql-like
user206168
user206168

Reputation: 1025

SQL ON JOIN LIKE (% before last 3 characters)

I am currently using

Left OUTER JOIN TableB ON TableA.ID = TableB.IDNumber

so ID and IDNumbers are 12 digits, example 607120670001 and only 0001 changes.

I am interested in matching only first 8 Character/digits. example 60712067%

data

TableA              TableB
607120670001     607120670002         (Match)
507120670001     507120670001         (Match)
123456780001     123456790001         (Not Match)

How can I do this?

Upvotes: 0

Views: 1427

Answers (2)

Alen Oblak
Alen Oblak

Reputation: 3325

Use trunc() function to make the 4 last digits equal to 0000.

Left OUTER JOIN TableB ON trunc(TableA.ID, -4) = trunc(TableB.IDNumber, -4)

Upvotes: 4

Adrian Cornwell
Adrian Cornwell

Reputation: 196

or, if defined as varchar then

Left OUTER JOIN TableB
ON TableA.ID LIKE SUBSTR(TableB.IDNumber,0,8) || '%'

Upvotes: 2

Related Questions