How to search if a row is a substring of another row of the same column in Oracle

I have a table that contains millions of rows for names of customers as a column. I want to find if a part of a name exists within another row in the same column. E.g. If a row has value ‘Roger Federer’ and there are other rows with values, ‘Roger’ and ‘Federer’, I want the corresponding primary keys of all the three rows.

Answer

If you want to construct a logic related to rows, union concepts may suit well,

by the way, in string operations we’d better use collations with patterns through upper or lower functions to satisfy case-insensitivity for letters :

select id from customers where lower(name) like '%roger%' union all
select id from customers where lower(name) like '%federer%';

and no need to add already included complete name ( e.g. Roger Federer ).

Edit : An Alternative method maybe the following :

  select distinct id
    from (select lower(regexp_substr('&str', '[^[:space:]-]+', 1, 1)) frst,
                 lower(regexp_substr('&str', '[^[:space:]-]+', 1, 2)) lst,
                 lower('&str') nm
            from customers) c1
   cross join customers c2
   where c1.frst like '%' || lower(c2.name) || '%'
      or c1.lst like '%' || lower(c2.name) || '%'
      or c1.nm like '%' || lower(c2.name) || '%';

by adding a search string('&str') to make the query more dynamic as you wish. ( when prompted enter Roger Federer for str substitution variable )