Take the 2-minute tour ×

I'm making a search function for my website, which finds relevant results from a database. I'm looking for a way to count occurrences of a word, but I need to ensure that there are word boundaries on both sides of the word ( so I don't end up with "triple" when I want "rip").

Does anyone have any ideas?

People have misunderstood my question:

How can I count the number of such occurences within a single row?

share|improve this question
add comment

9 Answers

up vote 3 down vote accepted

This is not the sort of thing that relational databases are very good at, unless you can use fulltext indexing, and you have already stated that you cannot, since you're using InnoDB. I'd suggest selecting your relevant rows and doing the word count in your application code.

share|improve this answer
add comment

You can overcome the issue of mysql's case-sensitive REPLACE() function by using LOWER().

Its sloppy, but on my end this query runs pretty fast.

To speed things along I retrieve the resultset in a select which I have declared as a derived table in my 'outer' query. Since mysql already has the results at this point, the replace method works pretty quickly.

I created a query similar to the one below to search for multiple terms in multiple tables and multiple columns. I obtain a 'relevance' number equivalent to the sum of the count of all occurrances of all found search terms in all columns searched

((length(x.ent_title) - length(replace(LOWER(x.ent_title),LOWER('there'),''))) / length('there')) 
+ ((length(x.ent_content) - length(replace(LOWER(x.ent_content),LOWER('there'),''))) / length('there'))
 + ((length(x.ent_title) - length(replace(LOWER(x.ent_title),LOWER('another'),''))) / length('another')) 
+ ((length(x.ent_content) - length(replace(LOWER(x.ent_content),LOWER('another'),''))) / length('another')) 
) as relevance, 
x.this_id as anchor,
'Foo' as ent_type, 
sp.sp_id as ent_id, 
sp.page_id as this_id, 
sp.title as ent_title, 
sp.content as ent_content,
sp.page_id as page_id
WHERE (sp.title LIKE '%there%' OR sp.content LIKE '%there%' OR sp.title LIKE '%another%' OR sp.content LIKE '%another%' ) AND (sp_content.title NOT LIKE '%goes%' AND sp_content.content NOT LIKE '%goes%')
  [search a different table here.....]
) as x
JOIN page ON page.page_id = x.page_id 
WHERE page.rstatus = 'ACTIVE'
ORDER BY relevance DESC, ent_title;

Hope this helps someone

-- Seacrest out

share|improve this answer
add comment

Something like this should work:

select count(*) from table where fieldname REGEXP '[[:<:]]word[[:>:]]';

The gory details are in the MySQL manual, section 11.4.2.

share|improve this answer
This checks how many rows contain the search string. The questioner wants to count how often the search string is contained in each row. –  flu Jan 20 at 15:19
add comment

You can try this perverted way:

(LENGTH(field) - LENGTH(REPLACE(field, 'word', ''))) / LENGTH('word') AS `count`
  • This query can be very slow
  • It looks pretty ugly
  • REPLACE() is case-sensitive
share|improve this answer
add comment

Something like LIKE or REGEXP will not scale (unless it's a leftmost prefix match).

Consider instead using a fulltext index for what you want to do.

select count(*) from yourtable where match(title, body) against ('some_word');
share|improve this answer
Can't do fulltext index... I'm using InnoDB. –  stalepretzel Dec 28 '08 at 19:53
add comment

I have used the technique as described in the link below. The method uses length and replace functions of MySQL.

Keyword Relevance

share|improve this answer
add comment

If you want a search I would advise something like Sphinx or Lucene, I find Sphinx (as an independent full text indexer) to be a lot easier to set up and run. It runs fast, and generates the indexes very fast. Even if you were using MyISAM I would suggest using it, it has a lot more power than a full text index from MyISAM.

It can also integrate (somewhat) with MySQL.

share|improve this answer
add comment

create a user defined function like this and use it in your query


CREATE FUNCTION `getCount`(myStr VARCHAR(1000), myword VARCHAR(100))

    WHILE (result > 0) DO
    SET result = INSTR(myStr, myword);
    IF(result > 0) THEN 
        SET cnt = cnt + 1;
        SET myStr = SUBSTRING(myStr, result + LENGTH(myword));
    END IF;
    RETURN cnt;    



Hope it helps Refer This

share|improve this answer
add comment

It depends on what DBMS you are using, some allow writing UDFs that could do this.

share|improve this answer
add comment

Your Answer


