Count the number of words per line in MySQL

Question

I am creating a search function for my site that finds relevant results from the database. I'm looking for a way to count the occurrences of a word, but I need to make sure that there are word boundaries on both sides of the word (so I don't get “triple” when I want to “rip”).

Does anyone have any ideas?

---

People misunderstood my question:

How can I count the number of such cases within a single line?

Answer 1

It’s not that relational databases are very good if you cannot use full-text indexing, and you have already stated that you cannot, because you are using InnoDB. I would suggest choosing the appropriate lines and making the number of words in your application code.

Answer 2

You can try this perverse way:

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

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

Answer 3

With LOWER() you can overcome the problem of the tc> function, which is case sensitive mysql.

Its messy, but at my end this query is pretty fast.

To speed things up, I get a result set in an element that I declared as a view in my "outer" query. Since mysql already has results at this point, the replace method is pretty fast.

I created a query like the one below to search for multiple terms in multiple tables and multiple columns. I get a relevancy number equivalent to the sum of counting all occurrences of all found search terms in all found columns

 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

Answer 4

create a user-defined function like this and use it in the request

 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 this helps. Refer to this question.

Answer 5

Something like this should work:

select count (*) from the table where the REGEXP field name is '[[: <:]] word [[:>:]]';

For information on gory, see the MySQL manual, section 11.4.2.

Answer 6

Something like LIKE or REGEXP will not scale (unless it is the left-most prefix).

Instead, use a full-text index for what you want to do.

 select count(*) from yourtable where match(title, body) against ('some_word'); 

Answer 7

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

Keyword Relevance

Answer 8

If you need a search, I would recommend something like Sphinx or Lucene, I believe that Sphinx (as an independent full-text index) will be much easier to configure and run. It works fast and generates indexes very quickly. Even if you use MyISAM, I would suggest using it, it has much more than the full text index of MyISAM.

It can also integrate (several) with MySQL.

Answer 9

It depends on which DBMS you use, some allow you to write UDFs that could do this.