CODEWITHSUNDEEP
mysql
Jignesh Vyas
Jignesh Vyas

Reputation: 23

mysql search query in text with and without word boundaries

I have a product table which I am searching with simple like clause, I want to search with boundary and without boundary results.

SELECT *
FROM products 
WHERE title LIKE '%cold%'

for example if this query return following rows,

coldrum
rum cold
coldwine
cold wind

I want the result like this (first with boundaries and rest without boundaries)

cold wind
rum cold
coldrum
coldwine

Well, Actual SQL is like this,

select SQL_CALC_FOUND_ROWS distinct p.*, pd.*, m.*, i.image, if(s.status,
s.specials_new_products_price, null) as specials_new_products_price, if(s.status,
s.specials_new_products_price, if (pv.products_price, pv.products_price,
p.products_price)) as final_price from products p left join products_variants pv on
(p.products_id = pv.products_id and pv.is_default = 1) left join manufacturers m 
using(manufacturers_id) left join specials s on (p.products_id = s.products_id) left
join products_images i on (p.products_id = i.products_id and i.default_flag = 1),
products_description pd, categories c, products_to_categories p2c where 
p.products_status = 1 and p.products_id = pd.products_id and pd.language_id = 1 and
p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and
(pd.products_tags like '%gold%' or pd.products_name like '%gold%') order by
p.products_date_added desc limit 0, 20

Upvotes: 1

Views: 107

Answers (2)

Darshan Patel
Darshan Patel

Reputation: 2899

Try this :

SELECT * FROM products WHERE title LIKE '%cold%' ORDER BY 
(LENGTH(title) - LENGTH(REPLACE(title, ' ', ''))+1) DESC;

SqlFiddle

Upvotes: 0

Sathish
Sathish

Reputation: 4487

try like this

SELECT *
FROM products  
WHERE title LIKE '%cold%'order by 
case when title like 'cold %' then 0 
when title like '% cold' then 1 else title end

Upvotes: 1

Related Questions