MySQL–findingvaluesincommaseperatedstring_MySQL

MySQL is very strong, popular database stytem using for mainly inPHP MySQL web developmentsolution. 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_hobbyid hobbies--- --------1Travelling2 Suring the Internet3 Reading4 Swimming5 Meeting Friends6 Cooking. .. .10 Watching Movieemployeeidhobby_ids--- ---------1 1,4,3,6,1021,3,4,1037. .. .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)[[:>:]]"