how to get comma separated record in all combination

Question

I have one table consist of 5 fields e.g.

|id|platform|sites  |path |date
|1 |0       |1,2,3  |//ab |1/2/2019
|2 |0       |1,2    |//ab |1/2/2019
|3 |0       |1,2,3,4|//ab |1/2/2019
|4 |0       |2,3,1  |//ab |1/2/2019

and I want to search table on column "sites" but there will be any combination on that field value. e.g 2,3,1 or 3,1,2 etc. and result should be with id 1 and 2 from above example. Also i need results in exact length as search value. if user puts 3 comma separated values then I want result which contains exact 3 comma separated values but in all combination.

I tried "IN" and "LIKE" clauses but it doesn't work for all combinations and exact length search

IN clause select * from tbl_demo where sites in (1,2,3) order by 1 DESC;

LIKE clause select * from tbl_demo where sites like "%1,2,3%" order by 1 DESC;

I think this is a perfect demonstration of why it is such a bad idea to store comma delimited lists in a table column. It makes accessing and processing that information very much more difficult than if you had designed your database more logically — RiggsFolly, Apr 24 '19 at 14:37
See also [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/a/3653574/20860) — Bill Karwin, Apr 24 '19 at 14:44
You also look like you have a Date held in a column that is not of type DATE. A serial offender on the database design front it would seem — RiggsFolly, Apr 24 '19 at 14:45
I added the link above but I didn't vote to close this question as a duplicate, because the question I linked to doesn't solve the OP's problem. It's just background to explain other reasons why a comma-separated list is considered impractical in a relational database. — Bill Karwin, Apr 24 '19 at 14:58
as per your comments I get that it is not a proper way to doing things. But i am a beginner in this area and this DB structure was their before i started working on it. After your negative markings you guys just demotivated me. I spend my time to solve this problem and I spent more time to earn this points. well thanks anyways for your support — Mohit Patil, Apr 30 '19 at 04:57

score 3 · Accepted Answer · answered Apr 24 '19 at 14:52

Obviously storing a string of numbers in a comma-separated list is wrong from a database normalization perspective. I saw that others tried to close the question as a duplicate of "Is storing a delimited list in a database column really that bad?" but that's not a duplicate because it rejects the OP's question instead of answering it.

Let's discuss options for solving this.

The first choice would be to normalize it properly. Create a child table and store a reference to the id in the table you show, plus one site value per row.

| id | site |
|  1 |    1 |
|  1 |    2 |
|  1 |    3 |
|  2 |    1 |
|  2 |    2 |
...

Then you can do groupwise counts, or at least do a GROUP_CONCAT() in sorted order so you can compare to your criteria.

SELECT id, GROUP_CONCAT(site ORDER BY site) AS sites
FROM MyTable GROUP BY id
HAVING sites = '1,2,3'

If you can't change the organization of data, it would help a lot if you could fix the strings so that the numbers were always in ascending order. But you probably can't do that (or don't want to try).

Another option is to compare one value at a time using the FIND_IN_SET() hack that some people use to search comma-separated strings. It's not intended for this purpose, it's a function to work with MySQL's SET data type, but it works on strings too, so people who are stuck with comma-separated strings of numbers use it.

SELECT ...
FROM YourTable
WHERE FIND_IN_SET('1', sites)
  AND FIND_IN_SET('2', sites)
  AND FIND_IN_SET('3', sites)

You'd need as many terms as the numbers you're searching for. But you also need to make sure the set doesn't contain more numbers.

  AND LENGTH(sites) - LENGTH(REPLACE(sites, ',', '')) + 1 = ?

In this expression, ? is the number of elements we're looking for: 3.

This type of solution may work, but it can't be optimized. It will always do a table-scan, so it'll get slower and slower the larger your table gets.

how to get comma separated record in all combination

1 Answers1