0

I have a table containing product names

I am searching for "Johnson"

id, productname
1 , Johnson & Johnson baby wipes
2 , Johnson Baby soft wipes
3 , Johnson's baby wipes

I want Johnson & Johnson baby wipes to be first match followed by others. Is there anyway to achieve this using mysql full text search.

ka4tik
  • 77
  • 1
  • 5
  • More details would be appreciated. Does the order of items other than "Johnson & Johnson" matter? Could you include the query that you've tried? – HamiltonPharmD Sep 17 '20 at 17:32
  • FTS calculates weight. If you need to sort according it - put `MATCH .. AGAINST` to the `ORDER BY` expression. If you need the rows to be sorted by the amount of searched word in the value primarily then you must calculate this words amount using common string functions (not FTS) and put this expression into ordering expressions list. – Akina Sep 17 '20 at 17:33
  • @HamiltonPharmD i just need row which has number of times word occurrence should appear before row which has less number of times word occuring. – ka4tik Sep 17 '20 at 17:46
  • https://stackoverflow.com/questions/13494460/mysql-fulltext-search-order-by-relevance this seem like what i am trying to achieve but not able to get it working – ka4tik Sep 17 '20 at 18:18

2 Answers2

0

Pls use this... You can remove no_of_match field if you want to.

select id, product_name,
(LEN(product_name) - LEN(REPLACE(product_name, 'Jhonson', ''))) / LEN('Jhonson') no_of_match
from table 
Order by   
 (LEN(product_name) - LEN(REPLACE(product_name, 'Jhonson', ''))) / LEN('Jhonson') 
desc
Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
0

One way to solve this in MySQL:

SELECT id,
    productname,     
FROM #TEMP1
ORDER BY ROUND (   
          (LENGTH(productname)- LENGTH( REPLACE ( productname, 'Johnson', '') ) )
          /LENGTH('Johnson')        
         ,2) DESC

Where #TEMP1 is the test table I created using the values you provided:

create table #TEMP1
  (id  VARCHAR(max), productname  VARCHAR(max))
INSERT INTO #TEMP1 (id, productname)
VALUES
  ('1' , 'Johnson & Johnson baby wipes'),
  ('2' , 'Johnson Baby soft wipes'),
  ('3' , 'Johnson"s baby wipes')
HamiltonPharmD
  • 582
  • 3
  • 19