0

I have a database table which looks like this (simplified):

╔════╦══════════════╦═════════╗
║ ID ║  Product     ║ Tags    ║
╠════╬══════════════╬═════════╣
║  1 ║ Product1     ║a,1-5,b  ║
║  2 ║ Product2     ║a,6-12,d ║
║  3 ║ Product3     ║a,20-30,c║
║  4 ║ Product4     ║b,5-55,a ║
╚════╩══════════════╩═════════╝

The query I'm struggling with should return the results based on Tags column.

Example

Should return all products that have the a tag (no matter of the position determined with , character) with number scope from 6-21 for the second tag, which represents the years of the ones potentially interested into product.

I'm clueless on how to do this.

Ognj3n
  • 759
  • 7
  • 27
  • 2
    Your problem is storing multiple values in a single string column. You should work on fixing that. – Gordon Linoff Jun 02 '20 at 15:57
  • 1
    The specified duplicate is quite on-target and I upvoted Bill Karwin's answer a long, long time ago. However, it really is not a duplicate of this question (perhaps a duplicate of what this question *should* be, but not this question). – Gordon Linoff Jun 02 '20 at 17:04

1 Answers1

3

You should not store multiple values in a string column. It is wrong, wrong, wrong. SQL has a great way to store lists. It is called a table, which has rows and columns for each value.

That said, sometimes we are stuck with other people's really, really, really bad decisions. For those purposes, MySQL has a convenient function, find_in_set():

where find_in_set('a', tags) > 0

Your effort should go into fixing the data model, rather than trying to work around it.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • _SQL has a great way to store lists. It is called a table..._ +1 :) – B001ᛦ Jun 02 '20 at 15:59
  • *Other peoples' really, really, really bad decisions.* +1 – O. Jones Jun 02 '20 at 16:01
  • Thanks for the help and advice, but as you said, sometimes we're stuck with people's bad decisions and this table design is one of those. However, I gotta stick with it for now. When it comes to the query where clause you suggest to use, it returns all the rows that have a tag, not taking the second part (the age range) into consideration. What's difference between this one and the wildcard search `WHERE 'a,%' OR '%,a'` when it comes to results? – Ognj3n Jun 02 '20 at 16:14