how select row in MySQL by check when column data include delimiter

Question

I have question . If i have user table and a column name be freinds . I want use some user id inside a column friends with this value : 1|2|45|18 . This means for example user id =5 have 4 freinds with these id 1|2|45|18. I don't want use two table for this situation.

How can select each users data when friends with user id = 5 ? In php we have explode function for slice each delimeter . Can i set delimeter for this target or i should use two table user and friends in MySQL ?

Answer 1

It's an awful case, especially if you inherit some DB with such a scheme, but to find friends you can use:

SELECT friend.*
FROM user u 
left join user friend on  concat('|', o.friends, '|') like concat('%|', friend.id, '|%')

where u.id = 5;