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.

Answer 1

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?