MySQL – finding values in comma seperated string

PHP MySQL Web Development India - Ezeelive Technologies

MySQL – finding values in comma seperated string

MySQL is very strong, popular database stytem using for mainly in PHP MySQL web development solution. In web application development sometime developer store data in comma(,) or pipe (|) etc formats. Finding value(s) from comma seperate MySQl has REGEXP (Regular Express) function. Use this function developer can search or filer one or multiple string value in your field data. Given below example to understand in detail how to use finding value(s) from comma seperate in MySQL Database field.

MySQL database table structure :

employee_hobby

id     hobbies
--- --------
1      Travelling
2     Suring the Internet
3     Reading
4     Swimming
5     Meeting Friends
6     Cooking
. .
. .
10   Watching Movie

employee

id    hobby_ids
--- ---------
1     1,4,3,6,10
2    1,3,4,10
3    7
. .
. .
10   3,5,6,9

1. Finding single id in Comma seperate string value (Get all employee id who has hobby “Swimming”) :

SELECT id FROM employee WHERE hobby_ids REGEXP "[[:<:]](4)[[:>:]]"

2: Find multiple id in comma separated string (Get all employee ids who has Travelling,Swimming,Watching Movie hobby) :

SELECT id FROM employee WHERE hobby_ids REGEXP "[[:<:]](1|4|10)[[:>:]]"

Leave a Reply

Your email address will not be published. Required fields are marked *