Joining tables with LIKE (SQL)

sql oracle ora-01722
Jacob Nelson picture Jacob Nelson · Mar 7, 2011 · Viewed 32.8k times · Source

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

DCookie picture DCookie · Mar 7, 2011

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.

Related questions

.nextval JDBC insert problem

I try to insert into table with sequence .nextval as primary key, the sql in Java is sql = "INSERT INTO USER (USER_PK, ACCOUNTNUMBER, FIRSTNAME, LASTNAME, EMAIL ) VALUES (?,?,?,?,?)"; ps = conn.prepareStatement(sql); ps.setString(1, "User.nextval"); ps.setString(2, accountNumber); ps.…

Read More
oracle jdbc driver version madness

Why the heck does Oracle offer a different(!) version of the JDBC driver, e.g. ojdbc14.jar, for every(!) database version? The files all have different sizes and thus probably different content. background: We get a random and seemingly irreproducible …

Read More
Can I pass a number for varchar2 in Oracle?

I have an Oracle table and a column (col1) has type varchar2(12 byte). It has one row and value of col1 is 1234 When I say select * from table where col1 = 1234 Oracle says invalid number. Why is that? Why I cannot …

Read More