1

I have the query

SELECT * FROM images WHERE tags LIKE '%example%hello%'

I would like the database to select rows which have 'example' and 'hello' in the 'tags' column in any order, as in:

A row with 'hello, test, example', also 'example, hello, test' or any other variation of this order. Is this possible, even without using LIKE? The rows must all contain everything specified with LIKE.

EDIT: Such as, when I provide 'example' and 'hello' in the query, rows returned must contain both 'example' and 'hello'.

Aldwoni
  • 1,168
  • 10
  • 24
q3d
  • 3,473
  • 8
  • 34
  • 39

2 Answers2

2

You could try a simple OR for a quick solution:

SELECT * FROM images WHERE tags LIKE '%example%' OR tags LIKE '%hello%'

EDIT

To address your edit, you can use AND instead:

SELECT * FROM images WHERE tags LIKE '%example%' AND tags LIKE '%hello%'
Clive
  • 36,918
  • 8
  • 87
  • 113
  • Sorry, made an edit to my question in the time you posted. I need all rows returned to contain both 'example' and 'hello' – q3d Oct 27 '11 at 00:06
  • @user1015599, just be careful in generalizing this technique, as it probably won't work the way you expect it to if your two words are 'example' and 'exam', and might even have trouble if the words are 'example' and 'plenty'. – jswolf19 Oct 27 '11 at 03:53
0

You could use FIND_IN_SET:

SELECT tags FROM images WHERE FIND_IN_SET('hello', tags) AND FIND_IN_SET('example', tags)

However, this requires that tags not have spaces after the comma (you will have to do replace on tags or more boolean checks in that case). This might be faster than LIKE. I'm not sure.

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405