MYSQL digit filtering query on text field

Question

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.

Your problem is storing multiple values in a single string column. You should work on fixing that. — Gordon Linoff
– Gordon Linoff, Commented Jun 2, 2020 at 15:57
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
– Gordon Linoff, Commented Jun 2, 2020 at 17:04

Gordon Linoff · Accepted Answer · 2020-06-02 15:58:33Z

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.

answered Jun 2, 2020 at 15:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

B001ᛦ Over a year ago

SQL has a great way to store lists. It is called a table... +1 :)

O. Jones Over a year ago

Other peoples' really, really, really bad decisions. +1

Ognj3n Over a year ago

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?

Collectives™ on Stack Overflow

MYSQL digit filtering query on text field

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related