Join using partial string match with the first n number PostgreSQL-CodePudding

I am trying to join two tables based on a partial string match in PostgreSQL. For example, I have the following:

table1.code
010129  
022933
029482

table2.new_code
010129847648
022933646495
029482732610

I want to join based on the first 6 characters matching across the two tables. I have tried something like the following out of desperation, but Postgres doesn't seem to like it.

SELECT table1.code, table2.new_code,
FROM table1 
INNER JOIN table2 
ON table1.code = LEFT(table2.code, 6)

Is there a way to do what I want to do?

CodePudding user response：

What you have should work, except for a few grammar issues. The column in table2 is named new_code and there's an extra comma in the select list.

SELECT table1.code, table2.new_code
FROM table1 
INNER JOIN table2 
ON table1.code = LEFT(table2.new_code, 6);