Counting the number of words in a Column in Oracle SQL

How do you group this data, based on Patterns? Is it possible in SQL?

 CREATE TABLE ABC ("NAMES" VARCHAR2(50 BYTE)) `

`INSERT INTO ABC (names) VALUES ('CA Apple 3');
 INSERT INTO ABC (names) VALUES ('New Apple 4');
 INSERT INTO ABC (names) VALUES ('Cra Apple 5');
 INSERT INTO ABC (names) VALUES ('UK Apple 5c');
 INSERT INTO ABC (names) VALUES ('Apple 6s');
 INSERT INTO ABC (names) VALUES ('Apple 7');
 INSERT INTO ABC (names) VALUES ('Apple x');
 INSERT INTO ABC (names) VALUES ('az Apple xr');
 INSERT INTO ABC (names) VALUES ('Apple xs');
 INSERT INTO ABC (names) VALUES ('Motorola RIZR');
 INSERT INTO ABC (names) VALUES ('eu Motorola RAZR');
 INSERT INTO ABC (names) VALUES ('Motorola RoZR');
 INSERT INTO ABC (names) VALUES ('Motorola RR');
 INSERT INTO ABC (names) VALUES ('fin Motorola RIZ');
 INSERT INTO ABC (names) VALUES ('Motorola R');
 INSERT INTO ABC (names) VALUES ('sau Google Pixel');
 INSERT INTO ABC (names) VALUES ('Google Pixel 2');
 INSERT INTO ABC (names) VALUES ('Google Pixel 3');
 INSERT INTO ABC (names) VALUES ('Samsung Galaxy');
 INSERT INTO ABC (names) VALUES ('aus Samsung Galaxy 3');
 INSERT INTO ABC (names) VALUES ('Samsung Small 2');
 INSERT INTO ABC (names) VALUES ('Samsung Earth');
 INSERT INTO ABC (names) VALUES ('ko Samsung Solar');
 INSERT INTO ABC (names) VALUES ('Samsung Milky Way');
 INSERT INTO ABC (names) VALUES ('Samsung Chill');
 INSERT INTO ABC (names) VALUES ('Yi Apple Chill');
 INSERT INTO ABC (names) VALUES ('In Apple');
 INSERT INTO ABC (names) VALUES ('razy Motorola');
 INSERT INTO ABC (names) VALUES ('Samsung');`

So, I have a table like this, Imagine there is 500000 rows and 4800 brand names.

The 4800 brand names can either be the first word, second word, third word or last word.

One possible way to solve this would be to get the substrings and count them, and Order by count(pattern) desc where rownum < 4800;

Now I need to find the count of the words (Eg: Apple, Samsung, Motorola)

Desired output is shown below:

Answer

The answer to this data set is below:

`select * from(
 select x,count(*) as coun from ( 
 select substr(names,
            INSTR(names, ' ', -1, 1)+1) as x
            from abc


  union all

  SELECT SUBSTR(names,
          INSTR(names, ' ', 1, 1) + 1,
          INSTR(names, ' ', 1, 2) - INSTR(names, ' ', 1, 1) - 1) as x
  FROM abc

  union all 

   SELECT SUBSTR(names,1,
          INSTR(names, ' ',1 , 1)-1) as x
   FROM abc
   )
   where x is not null and x not in ('1','2','3','4','5','6','7')
  group by x
  order by coun desc)
  where rownum < 4800;'

Answer: