Search code examples
mysqlsqlregexsql-like

StringInString Query?

I have this database where I have stored some tags in it. I stored the tags like this:

"humor,funny,animal"

Now I need a mysql query that selects this line when I search for "humor", "funny" or "animal". What I have until now:

SELECT id FROM database WHERE tags REGEXP 'humor' LIMIT 1

Unfortunately, it does not work. Could someone of you please help me out?

Edit: Thanks for all the responses! I will now need to study this first! But problem solved :)

Solution

  • You can use LIKE

     SELECT id FROM database WHERE tags LIKE '%humor%' LIMIT 1

    Which will search for any entry where 'humor' is a substring. Note this will also return items tagged 'humorous'.

    But like others said, having a separate table for tags would be best. To do this you will also need a pivot table.

    So for example 

    --------------  data  -------------
|     ID         |     NAME       | 
| 1              | example        |
| 2              | example 2      | 
-----------------------------------

--------------  tags  -------------
|      ID        |     NAME       |
| 1              | humor          |
| 2              | cats           |
| 3              | wumpus         |
-----------------------------------

------------  data_tags  ----------
|   DATA_ID      |   TAG_ID       |
| 1              | 1              |
| 1              | 2              |
| 2              | 1              |
| 2              | 3              |
-----------------------------------