Count occurrences of a word in a row in MySQL

Question

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?

Solution 1

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.

Solution 2

You can try this perverted way:

SELECT 
(LENGTH(field) - LENGTH(REPLACE(field, 'word', ''))) / LENGTH('word') AS `count`
ORDER BY `count` DESC

• This query can be very slow
• It looks pretty ugly
• REPLACE() is case-sensitive

Solution 3

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

SELECT DISTINCT ( 
((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.ent_type, 
x.ent_id, 
x.this_id as anchor,
page.page_name
FROM ( 
(SELECT 
'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
FROM sp
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%')
) UNION (
  [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

Solution 4

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

DELIMITER $$

CREATE FUNCTION `getCount`(myStr VARCHAR(1000), myword VARCHAR(100))
    RETURNS INT
    BEGIN
    DECLARE cnt INT DEFAULT 0;
    DECLARE result INT DEFAULT 1;

    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;
    END WHILE;
    RETURN cnt;    

    END$$

DELIMITER ;

Hope it helps Refer This

View more solutions

Comments

[stalepretzel]

Can't do fulltext index... I'm using InnoDB.

[flu]

This checks how many rows contain the search string. The questioner wants to count how often the search string is contained in each row.

[RandomSeed]

This will count strings, not words.

[RandomSeed]

This will count strings, not words.