MySQL – finding values in comma seperated stringEzeelive Technologies
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)[[:>:]]"