Joining tables with LIKE (SQL)

Question

First of all I am using Oracle:

Table One Name = tableone

Table Two Name = tabletwo

tableone has a column named pizzaone, tabletwo has a column named pizzatwo. I want to join tableone to tabletwo where pizzaone is somewhere in the pizzatwo's name.

What I tried:

select * 
from tableone 
   join tabletwo on tableone.pizzaone like ('%' + tabletwo.pizzatwo + '%')

How can I correct this query?

Answer 1

Try this syntax instead:

select * 
from tableone 
   join tabletwo on tableone.pizzaone like ('%' || tabletwo.pizzatwo || '%')

Oracle's string concatenation operator is the double pipe (||). The invalid number error is because Oracle expects numeric operands for the '+' operator.