Search code examples
databasepostgresqlpostgresql-8.4postgresql-9.0

Postgres Join with LIKE

In PostgreSQL I have a table 

tbl1
id -- RCODE -- COUNTRY --
1     US/MSR   United states of America
2     GY/LSR   Germany
3     CA/FSA   Canada

tbl2
id -- Name -- CCODE
33    T1      US        
44    Y1      CA       
55    W1      GY

can the tables be joined with LIKE condition on fields RCODE on tbl1 with CCODE on tbl2 ? such that i gets the result as 

id --NAME-- RCODE -- CCODE--- COUNTRY

i shall provide the id of tbl2 ie) when i give the id 44 the result will be 

id --NAME-- RCODE -- CCODE--- COUNTRY
44   Y1     CA/FSA   CA       Canada

can any one help me to solve this query , it is PostgreSQL

one thing is that first two char in RCODE is same to that of CCODE in table2.

Solution 
  • select tbl2.name, tbl1.rcode, tbl2.ccode, tbl1.country
from tbl1 
  join tbl2 on substring(tbl1.rcode, 1, 2) = tbl2.ccode