0

I´d like your help to count word occurrences in titles for each 'id' in a MySql table.

The table Article and ExpectedResult are available in http://www.sqlfiddle.com/#!9/f985f/1

Thanks in advance.

Daniel Daranas
  • 22,454
  • 9
  • 63
  • 116
Romualdo Alves
  • 271
  • 1
  • 2
  • 8
  • . . Do you have a list of words you are looking for? Are you just trying to calculate the number of words in the title? As a general rule, including sample data and desired results makes a question much better. – Gordon Linoff May 22 '13 at 13:59
  • as i understand and according to his expected result, he wants each word and how many times they used in title. btw, im waiting answer for this question, it seems cool – rcpayan May 22 '13 at 14:04

1 Answers1

0

One way of doing it:

select a.id, 
       w.word, 
       (LENGTH(a.title) - LENGTH(REPLACE(a.title, w.word, ''))) / LENGTH(w.word) `count`
from Articles a join
(select distinct substring_index(substring_index(title,' ',n),' ',-1) word
 from Articles
 cross join 
 (select 1 n union all select 2 union all select 3 union all select 4 union all
  select 5 union all select 6 union all select 7 union all select 8 union all
  select 9 union all select 10 union all select 11 union all select 12) n
) w on concat(' ',a.title,' ') like concat('% ',w.word,' %')

Note that the number of values in n should be the maximum number of words that can appear in a single record's title - I have used 12 here, as it is more than the largest number of words in any record's title in the supplied data, but a larger set of numbers may be required (depending on the actual data).

SQLFiddle here.