CODEWITHSUNDEEP
mysqlsql
Ognj3n
Ognj3n

Reputation: 759

MYSQL digit filtering query on text field

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.

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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.

Upvotes: 3

Related Questions