How to join tables when entries don't exactly match

Question

I have two tables I need to join based on zip code.

Table1:

ZIP	INCOME
ZCTA5 01845	45,000
ZCTA5 11421	52,000
...	...
ZCTA5 99704	42,000

There are thousands of entries in Table1

Table2:

ZIP	column1
01845	Y
11421	N
...	...
99704	Y

Table1 has several more entries in it than Table2 that I don't need, I'm just trying to get incomes for the zip codes listed in Table2. I figure I can just do an inner join for this, but I'm running into the issue that I have that ZCTA5 preceding the zip code in table1. I was curious if there was a way to do this?

Answer 1

Use the substr function to extract the zip code from table1, and join on that:

SELECT * FROM table1 t1 INNER JOIN table2 t2 ON substr(t1.zip, -5) = t2.zip;