-2

I have a Mysql database that contains some category ids on it which stores comma-separated values on a table.

sql table view

By using select * from style where categories like '%8,%'; it returns all the values end with 8. For example, if the table rows have two values like 8 and 148 it returns both rows. But I want to get only the rows that contain 8. How to do it

Gehan Pasindhu
  • 31
  • 6
  • 5
    Fix the data model. These values should be stored in a separate table with a foreign key back to this table. Normalizing the data will make it easier to query the data. – David Mar 21 '22 at 20:29
  • getting data from an API and that's how the data stored in that api – Gehan Pasindhu Mar 21 '22 at 20:35
  • https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set – Ergest Basha Mar 21 '22 at 20:35
  • look up function [FIND_IN_SET](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set) the data type field you have appears to be a "SET" Type. storing multiple values in 1 field. FIND_IN_SET is used to find values in that typed field. – xQbert Mar 21 '22 at 20:44
  • 1
    I understand that you get data from the API like that, but you are going to forever have problems if you store your detail that way. This problem will just crop up again in a different form. Forever and ever. – Andy Lester Mar 21 '22 at 21:34
  • 1
    `getting data from an API and that's how the data stored in that api` Just because you *receive* it that way doesn't mean you have to *store* it that way in your database. Make life easier on yourself and split out the values and store them as individual rows. – SOS Mar 22 '22 at 01:19

3 Answers3

1

Storing multiple values in a single column is a denormalised design that will almost always cause you problems. However you need to add commas to both sides and compare:

select *
from Style
where concat(',',Categories,',') like '%,8,%';
Stu
  • 30,392
  • 6
  • 14
  • 33
0

Like everyone else: normalize your data. But if you can't mySQL supports find_in_set() for set datatypes which this appears to be.

SQL

With CTE as (SELECT 'T-Shrits' as baseCategory, '8,21,75,87,148' categories UNION ALL
SELECT 'T-Shrits' as baseCategory, '8,21,75,87,148'  categories UNION ALL
SELECT 'T-Shrits - Long Sleeve' as baseCategory, '8,21,75,87,148,92'  categories UNION ALL
SELECT 'T-Shrits' as baseCategory, '21,75,87,100,148'  categories)

SELECT * FROM CTE where find_in_set(8,categories) >0

OR we can use a boolean evaluation and eliminate the > 0

SELECT * FROM CTE where find_in_set(8,categories)

Giving us:

+------------------------+-------------------+
|      baseCategory      |    categories     |
+------------------------+-------------------+
| T-Shrits               |    8,21,75,87,148 |
| T-Shrits               |    8,21,75,87,148 |
| T-Shrits - Long Sleeve | 8,21,75,87,148,92 |
+------------------------+-------------------+

Notes

  • Find_in_set() returns the Returns a value in the range of 1 to N in the pseudo array of the value being searched. We need to ensure the result is greater than 0 (or treat it as a Boolean) in order for the searched value to "exist" within a record column.
  • The engine didn't return my 4th union value in CTE because it doesn't have an "alone" 8 value
  • If we searched for just 100 it would return that last record.
  • This function comes at a cost of performance on large datasets; which if data was normalized and indexed, you wouldn't have.
  • So why does this exit? For small enumerated lists or properties. It's still not ideal but if you have just a few using it "can" make sense. but in a very limited use case and often is missused.
  • This design violates 3rd normal form. Which is why most RDBMS designs cringe when it's brought up as it's not scalable.
  • as to why people are up in arms about multi value columns: Read this or This
xQbert
  • 34,733
  • 2
  • 41
  • 62
0

You can also use rlike and in fact it is much better than like as it has much more options.

* = repetition of what is in front of it zero or more times
. = Equivalent to any character including none
^ = Anchor start (Forces that begins with ...)
$ = final anchor (forces it to end with ....) 
[ ] = [ RST ] Contain an R or S or T but only one
[^] = DENY IT

And many more options

select * from style where concat(',',categories,',') rlike '*,8,*';
Javier G.Raya
  • 230
  • 1
  • 3
  • 15