Getting CSV values in mySQL

Question

So I have some CSV values stored in a mySQL database.

For example:

ID    Name     parentID
1     Dave     1,4,6
2     Josh     2
3     Pete     10
4     Andy     2,10

Using this query

SELECT * FROM `table` WHERE `parentID` LIKE %4%

Only Dave will be returned, this is correct. However if I select using: LIKE %1%, pete and andy are selected as well as dave, because they conatin '1'.

I need the query to be able to distinguish '10' for example, from '1'. It needs acknowledge each value between a comma is distinct and appreciate the fact the last comma may be omitted.

Am I right in thinking perhaps REGEX could do the job instead?

Thanks.

The only actual non jury-rigged method would be to not save CSV values in a relational database. Make a table matching ID's to parentID's — Nanne, Jun 13 '13 at 20:18
The context is that parentID value in this table, refers to the primary ID of another table. — Billy Jake O'Connor, Jun 13 '13 at 21:13
That makes it even more clear you need a different database scheme! — Nanne, Jun 14 '13 at 07:47

score 3 · Accepted Answer · edited May 23 '17 at 11:56

3

You can use a regex to match "word boundaries":

WHERE parentID RLIKE '[[:<:]]4[[:>:]]'

Or you can use a special function that parses elements of a comman-separated string:

WHERE FIND_IN_SET('4', parentID) <> 0

I agree with the comment from @Nanne.

You will also find that it's better to store data not in comma-separated lists, but in a normalized fashion. I don't know if you have freedom to change your schema at this time, but for what it's worth, read my answer for the question Is storing a delimited list in a database column really that bad?

edited May 23 '17 at 11:56

Community

1
1

answered Jun 13 '13 at 20:18

Bill Karwin

538,548
86
673
828

Thank you. FIND_IN_SET() was a perfect implementation – Billy Jake O'Connor Jun 13 '13 at 20:23
@BillyJakeO'Connor Did you want to mark this one as answered? – AbsoluteƵERØ Jun 13 '13 at 20:45

Getting CSV values in mySQL

1 Answers1