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?

Correct in what way? Did you get an error message, or is there a performance issue, something like that? — MartW, Mar 07 '11 at 20:35
ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: *Action: — Jacob Nelson, Mar 07 '11 at 20:38
Might be an idea to state what SQL engine you are using. Oracle from the last error code I assume. — Orbling, Mar 07 '11 at 20:38
No Charles I cant sorry. But both columns are VARCHAR2(40 BYTE) any thing else in specific you need to know? — Jacob Nelson, Mar 07 '11 at 21:10

score 33 · Accepted Answer · answered Mar 07 '11 at 21:10

33

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.

answered Mar 07 '11 at 21:10

DCookie

42,630
11
83
92

2

The double pipe is ANSI as well for SQL string concatenation. – OMG Ponies Mar 08 '11 at 00:44

Joining tables with LIKE (SQL)

1 Answers1

Linked