0

I am trying to fetch a list of results that has ALMOST similar value to the one I provides.

     id    |  food
1)    1       onions, pepper, ketchup
2)    2       burger, spinach, tomato
3)    3       chicken, barbecue, ham 
4)    4       onions, pepper, chicken
5)    5       book, pencil, paper

My goal is to return any row that has ALMOST identical to "chicken, pepper, onions"

I have tried the LIKE function, but it will only return rows only when the value that you provide is in correct order and can be found somewhere on the columns.

My code is like this:

SELECT * FROM table WHERE food LIKE ="%chicken, pepper, onions%"

Cœur
  • 37,241
  • 25
  • 195
  • 267
Musang Sang
  • 11
  • 3
  • You could make an array out of your `food` column and run a comparison of your imput against those from your array. Other option would be to use distance-based comparison algorithms such as Levenstein. – Kamil Gosciminski Nov 22 '15 at 21:42
  • The thing is, I have hundreds of food column, and you don't want to store all of it – Musang Sang Nov 22 '15 at 21:43
  • I'm sorry, but what's the problem actually? It seems to differ from the one you've described, as I can see you have one food column in your example schema. – Kamil Gosciminski Nov 22 '15 at 21:45
  • If I used this code SELECT * FROM table WHERE food LIKE ="%chicken, pepper, onions%" do you think it would return anything? That's the problem – Musang Sang Nov 22 '15 at 21:46
  • I've already told you how to address this problem. Store your string in array and compare your input against every array element (procedural) OR use a distance-based algorithm on your string column. – Kamil Gosciminski Nov 22 '15 at 21:51
  • I have thousands and thousands of food coloumns and you DON'T WANT to store all of it and start comparing, it will slow down the performance – Musang Sang Nov 22 '15 at 21:53
  • You're probably misunderstaning the meaning of a `column`. What you mean is that you have many `rows` which contain `food` column. Again, solutions above don't require you to change your storage manner. – Kamil Gosciminski Nov 22 '15 at 22:01
  • 1
    If you normalize the ingredients table. i.e. have one ingredient per row then you could join to your list of required ingredients and count the matching groups? maybe interesting? [code to generate normalize table from delimited list](http://stackoverflow.com/a/33806675/3184785) – Ryan Vincent Nov 22 '15 at 22:41

0 Answers0