How to use LIKE wild card on subquery?

Question

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?

Thank you for posting a question the right way! You provided sample data, as well as your attempt and the full error message. Wishing more people would make the small effort to learn how to post a question! — , Aug 14 '18 at 19:20

score 5 · Answer 1 · answered Aug 14 '18 at 15:43

5

You could use JOIN:

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

answered Aug 14 '18 at 15:43

Lukasz Szozda

162,964
23
234
275

1

The OP's query, syntactically incorrect as it is, suggests that only the values from `fullnames.full_name` should be returned; so `select *` is probably excessive. Moreover, it seems that a `full_name` should be returned only once if there's a match in the other table, even if there may in fact be multiple matches (such as for `Tom Joe Smith`). If so - which is likely given the OP's attempt - then Gordon's solution is the right one. The OP needs a semi-join, not a join. – Aug 14 '18 at 19:17
@mathguy It depends. If OP need only full name without any other info then yes semi-join is correct(it's hame we don't have [explicit keyword](https://stackoverflow.com/questions/42249690/what-is-semi-join-in-database)). On the other hand if he wants to know which name was selected then `JOIN` is correct. Anyway it still can be rewritten as `SELECT DISTINCT f.full_name` but then we could discuss performance perspective. – Lukasz Szozda Aug 14 '18 at 19:26

score 3 · Accepted Answer · edited Aug 14 '18 at 19:18

3

You can use exists:

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

edited Aug 14 '18 at 19:18

answered Aug 14 '18 at 15:42

Gordon Linoff

1,242,037
58
646
786

How to use LIKE wild card on subquery?

2 Answers2