CODEWITHSUNDEEP
sqloracle-database
John Wick
John Wick

Reputation: 745

How to join a substring of a column (in one table) with the full value of another column (in another table)

So I currently have a table with the following columns and entries:

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

And I have another column with the following columns and entries:

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

How would I be able to join the two tables on the CODE and CODE_METADATA columns?(respectively). I've tried the following but it didn't work:

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;

Thanks in advance!

Upvotes: 1

Views: 979

Answers (3)

xiaoli
xiaoli

Reputation: 11

Try this: substr(t2.CODE_METADATA,1,length(t1.CODE)) = t1.CODE

Upvotes: 1

dnoeth
dnoeth

Reputation: 60482

You can also join using LIKE: 

t2.CODE_METADATA LIKE t1.CODE || '|%'

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65343

You might join with the condition :

substr(t2.CODE_METADATA,1,instr(t2.CODE_METADATA,'|')-1) = t1.CODE

Upvotes: 2

Related Questions