0

I have a string in MySQL database column as 

@ , 1 , 2, 3, 4 , @

I would like to query record using other comma separated string like

1, 3 
1, 2

And they should return result. What should be use Like or RLIKE and how.

Kaolin
  • 11
  • 1
  • 4
    https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad may be worth a read. – Nigel Ren Nov 28 '18 at 13:15
  • Importantly, what results are you actually expecting? Presumably `1, 2` should match, but should `1, 3`? Both values are present, but not consecutively (so using `LIKE` definitely isn't going to work). Would `1, 5` match because there's some partial overlap? – iainn Nov 28 '18 at 13:17
  • @iainn both string should match the result. Thank you – Kaolin Nov 28 '18 at 13:21
  • @iainn 1, 5 should match also – Kaolin Nov 28 '18 at 13:34
  • "I have a string in MySQL database column as @ , 1 , 2, 3, 4 , @"....this is a design error. You should be storing these in a properly normalised format in a child table, with a foreign key back to the main table. Read the link Nigel posted, and/or study database normalisation and entity-relationship design. If you design your database properly to begin with, then you won't have this kind of awkward problem when trying to get the data out again. It would be a simple SELECT query instead. – ADyson Nov 28 '18 at 13:55
  • @ADyson yes thank for ur suggestion. As it is old running project and also I can’t change dB design. :( – Kaolin Nov 28 '18 at 14:39
  • 1
    you can, if you're prepared to change other code which uses this field. It depends if that's more work than implementing a workaround for this situation, and whether it's then easier to maintain everything afterwards. It's worth thinking about. – ADyson Nov 28 '18 at 14:42

0 Answers0