Postgres Join with LIKE

Question

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.

Answer 1

For a similar problem, I used something like:

select
tbl2.id as id,
tbl2.name as NAME,
tbl1.rcode as RCODE,
tbl1.ccode as CCODE,
tbl1.country as COUNTRY
from tbl1, tbl2
where substring(tbl1.rcode, 1, 2) = tbl2.ccode;

The numbers in the substring expression are the 1 based index of the first character of the substring, and the length of the substring.

Other string operators, such as trim(), lower(), and upper() can also be useful. A more complete list of available string operators is at:

http://www.postgresql.org/docs/9.3/static/functions-string.html

Answer 2

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