0

I want to search in a table in the fields title and text via search query and the rows with the most matches win. My table pages looks like this:

------------------------------------------
| id | title      | text        | status |
------------------------------------------
| 1  | test test  | test xx     | 200    |
| 2  | nothing    | nothing     | 200    |
| 3  | test test  | test test   | 200    |
| 4  | abcefgh    | ijkl test   | 200    |
| 5  | test       | test abc    | 200    |
------------------------------------------

The expected result would look like this (selecting id, title, text via search query test and the limit 3):

---------------------------------
| id | title      | text        |
---------------------------------
| 3  | test test  | test test   |
| 1  | test test  | test xx     |
| 5  | test       | test abc    |
---------------------------------

I would really appreciate your help as I could not find a solution for a longer time now. Thanks in advance!

SimonBoh
  • 97
  • 8
  • Did you try anything? A simple google search would solve your problem in 2 mins. – forpas Apr 25 '19 at 20:32
  • 1
    Yes, I searched - but in the wrong way unfortunately. I tried it in a different way now and found an answer. I honestly believed I would be the first one to ask this question. Thank you to forpas and Barmar – SimonBoh Apr 25 '19 at 20:37
  • Possible duplicate of [Count the number of occurrences of a string in a VARCHAR field?](https://stackoverflow.com/questions/12344795/count-the-number-of-occurrences-of-a-string-in-a-varchar-field) – Alex Apr 25 '19 at 20:58

1 Answers1

1

You can do:

select
  id, title, text
from t
order by 
  round(length(title) - length(replace(title, "test", "")) / length("test") + 
  round(length(text) - length(replace(text, "test", "")) / length("test")
  desc
limit 3
The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • Thank you for your answer. I get an SQL error when executing your query but I assume it is close to being correct. I use the length/replace part in the **select** now with creating a variable from it (which is then used in **order by**). – SimonBoh Apr 25 '19 at 22:22