Count instances of first word in column Oracle SQL using SUBSTR and REGEXP_COUNT

Question

Using Oracle SQL, I have a column of strings called "title" in a table called OBJECT. Words are separated by spaces.

I want to write a query count how many titles have the same first word as other titles in the table. I want to output the word, and a count of how many entries begin with that word as FIRSTWORD, WORD_COUNT. E.G.

FIRSTWORD     WORD_COUNT
Word1   23
Word2   15

I am using both SUBSTR and INSTR to get the first word of each entry, and regexp_count to try and count the number of times it appears. So far, I can get it to output a list of the first words, but my count is always 0:

SELECT 
(SUBSTR(title, 0, INSTR(title, ' ')-1)) AS FIRSTWORD, 
(REGEXP_COUNT(title, INSTR(title, ' ')-1)) AS WORD COUNT 
FROM OBJECT GROUP BY title;

Will Angley · Accepted Answer · 2014-05-25 02:21:22Z

4

A subquery should do the trick.

WITH firstwords AS (
  SELECT (SUBSTR(OBJECT.title, 0, INSTR(OBJECT.title, ' ')-1)) AS WORD
  FROM OBJECT)
SELECT word, count(word)
FROM firstwords
GROUP BY word;

answered May 25, 2014 at 2:21

Will Angley

1,3947 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

reallybadatmath Over a year ago

Thankyou! That was perfect. I didn't know how to do this properly.

Gordon Linoff · Accepted Answer · 2014-05-25 02:50:00Z

2

FYI. You don't actually need a subquery. You can do the calculation at one level:

select SUBSTR(OBJECT.title, 0, INSTR(OBJECT.title, ' ')-1)) AS WORD, count(*)
from OBJECT
group by SUBSTR(OBJECT.title, 0, INSTR(OBJECT.title, ' ')-1)) ;

answered May 25, 2014 at 2:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Count instances of first word in column Oracle SQL using SUBSTR and REGEXP_COUNT

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related