Como juntar uma substring de uma coluna (em uma tabela) com o valor total de outra coluna (em outra tabela)

Portanto, atualmente tenho uma tabela com as seguintes colunas e entradas:

ROW_ID        CODE           VAL
  1            US             50
  2            CAN            15
  3            MEX            12

E eu tenho outra coluna com as seguintes colunas e entradas:

ROW_ID       CODE_METADATA         REGION
  1           US|451223123           8
  2          CAN|123123123           7
  3          MEX|41028               3

Como posso unir as duas tabelas nas colunas CODE e CODE_METADATA? (Respectivamente). Tentei o seguinte, mas não funcionou:

select t2.nvl(substr(code_metadata, 0, instr(code_metadata, '|')-1), code_metadata) as CODE
 from table1 t1
 join table2 t2 on t1.code = t2.CODE;

Desde já, obrigado!