0

I have a query which looks for a number of keywords which works fine using fulltext indexes but there is an issue with products like 'Adidas Adistar Salvation 3'

The 3 is actually important but the mysql fulltext index doesn't include it in the search.

What would be the best way to make sure a row contained "Adidas","Adistar" & "Salvation 3"

Normally the search would be:

SELECT * FROM products WHERE (title LIKE "%adidas%" AND title LIKE "%Adistar%" AND title LIKE "%Salvation%" AND title LIKE "%3%")

Many thanks Brett

bertster
  • 383
  • 1
  • 3
  • 17
  • 1
    "What would be the best way to make sure a row contained "Adidas","Adistar" & "Salvation 3"". Isn't that: LIKE "%Salvation 3%" instead of LIKE "%Salvation%" AND title LIKE "%3%". Also adidas case does not match – Edwin Stoteler Apr 12 '13 at 13:43
  • cheers - what do you mean that the adidas case doesn't match? isn't mysql case insensitive? – bertster Apr 12 '13 at 13:46
  • Well LIKE isn't casesensitive (I think) so it doesn't matter but Adidas and %adidas% had different cases – Edwin Stoteler Apr 12 '13 at 13:48
  • `Adidas` has a capital letter on the data example but `adidas` is all lower cased on the search pattern. This sould be not an issue if you use a ci (case insensitive) charset. – Alepac Apr 12 '13 at 13:49

1 Answers1

0

From this question, you could try using REGEXP:

SELECT * 
FROM products 
WHERE title REGEXP "adidas|Adistar|Salvation 3"

You could also try find_in_set()

Community
  • 1
  • 1
Valdogg21
  • 1,151
  • 4
  • 14
  • 24