CODEWITHSUNDEEP
sqloracle-database
filippo
filippo

Reputation: 5783

How to use LIKE wild card on subquery?

Table: FirstNames
NAME
    Tom
    Joe
    Peter

Table: FullNames
FULL_NAME:
    Tom Petty.
    Joe Satriani.
    Peter Griffin.
    Sarah Connor.

I would like to run a query:

select * 
from FullNames where FULL_NAME like '%' || (select NAME from FirstNames) || '%'

It yields:

ORA-01427: single-row subquery returns more than one row

which seems correct. Is there a way to do that in Oracle?

Upvotes: 3

Views: 401

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use exists:

select f.*
from FullNames f
where exists (select 1
              from firstnames fn
              where f.FULL_NAME like '%' || fn.NAME || '%'
             );

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use JOIN:

SELECT * 
FROM FullNames f
JOIN FirstNames g
  ON f.FULL_NAME LIKE '%' || g.NAME || '%';

Upvotes: 5

Related Questions