order by specific word count in mysql

Question

I need to order the results based on the repeatness of the word using mysql.

Here is my sample table

id   Name    keywords      Description
1    John    John, USA     John is good boy. John, John    
2    Alex    Alex, John    Alex is a friend of john.
3    Rocky   John          Rocky
4    John    John,John     John, John, John, John, John

Will take an "John" as example. In first row "John" is repeated 5 times, 2 times repeated in second row, 1 time repeated in 3rd row and 8 times repeated in 4th row. I need to show the results based on the count descending.

   Select * From table Where name like '%John%' OR keywords like '%John%' OR Description like '%John%'

So it will show in below order

 id   Name    keywords      Description
 4    John    John,John     John, John, John, John, John 
 1    John    John, USA    John is good boy. John, John  
 2    Alex    Alex, John    Alex is a friend of john.  
 3    Rocky   John          Rocky

https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html#function_match — M0rtiis, Aug 18 '15 at 05:58
There is a similar question here: [count-the-number-of-occurences-of-a-string-in-a-varchar-field](http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field) — Carsten Massmann, Aug 18 '15 at 06:02

Carsten Massmann · Accepted Answer · 2015-08-18T08:46:30.417

3

This will do the trick:

SELECT id,Name,keywords,Description,    
 ROUND (   
     (
         LENGTH(CONCAT(Name,keywords,Description))
         -LENGTH(REPLACE( CONCAT(Name,keywords,Description), "John", "") ) 
     ) / LENGTH("John")        
 ) AS count    
FROM tbl ORDER BY `count` desc

see here: Demo

Update

If you want to look for multiple (different) words per record you you should use a user-defined function (UDF) like

CREATE function wcnt(wrd varchar(32), str varchar(1000)) returns int
RETURN ROUND ((LENGTH(CONCAT(str))-LENGTH(REPLACE( CONCAT(str),wrd,"")))/LENGTH(wrd));

see here: function-demo

or here for a combination of the first query with the UDF

edited Aug 18 '15 at 08:46

answered Aug 18 '15 at 06:12

Carsten Massmann

26,510
2
22
43

Thanks @cars10 it is working. I have million records in my table, is it affect in performance? – skr07 Aug 18 '15 at 06:17
okay. is the same will be useful for multiple words count like `"John , Good"` ?. So the count will be both the keywords available in the any one of the column in a row. – skr07 Aug 18 '15 at 08:25

score 0 · Answer 2 · answered Aug 18 '15 at 05:59

0

SELECT * FROM T 
ORDER BY FIND_IN_SET('John',Description) DESC

FIDDLE

answered Aug 18 '15 at 05:59

Mihai

26,325
7
66
81

Unfortunately it does not *really* work, see here: http://sqlfiddle.com/#!9/e07e2/1 – Carsten Massmann Aug 18 '15 at 06:06
@cars10 If you break it so John is no longer a word yes,but he is searching for words – Mihai Aug 18 '15 at 06:11
Hi @Mihai, just checked it out further. Interestingly your `SELECT` works and sorts things in the right order, but for some reason I don't seem to be able to get a sensible `count` from your `FIND_IN_SET` function. It only returns `0` or `1`. - You got my upvote anyway! – Carsten Massmann Aug 18 '15 at 06:20
@cars10 I thinks it does something behind the scene,like user variables, the order is by the number of ocurrences,but the select doesnt show that. – Mihai Aug 18 '15 at 06:22

order by specific word count in mysql

2 Answers2