MySQL find values in comma separated string

MySQL is very strong, popular database system using for mainly in PHP MySQL web development solution. In web application development sometime developer store data in the comma(,) or pipe (|) etc formats. Finding value(s) from comma separated MySQL has REGEXP (Regular Express) function.

REGEXP (MySQL find values in comma separated string) function 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 the comma separated 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 separated string value (Get all employee id who has hobby “Swimming”) :

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

2: Find multiple ids 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)[[:>:]]"

MySQL REGEXP Operators:

^: Match the beginning of a string.

SELECT 'india' REGEXP '^in';

$: Match the end of a string.

SELECT 'indiaagra' REGEXP '^ia$';

. : Match any character (including carriage return and newline characters).

SELECT 'india' REGEXP '^d.*$';

a *: Match any sequence of zero or more a characters.

SELECT 'Indian' REGEXP '^In*n';

a+: Match any sequence of one or more a characters.

SELECT 'ezeelive' REGEXP '^ez+e';

a? : Match either zero or one characters.

SELECT 'ezeelive' REGEXP '^ez?e';

de| abc: Match either of the character sequences, de or abc.

SELECT 'in' REGEXP 'in|agra';

(abc)*: Match zero or more instances of the character sequence abc.

SELECT 'inin' REGEXP '^(in)*$';

{1}, {2,3}: Provides a more general way of writing regular expressions that match many occurrences of the previous atom (or “piece”) of the pattern.

SELECT 'india' REGEXP 'i[ind]{3}ia';

[a-dX],[^a-dX]: Matches any character that is (or is not, if ^ is used) either a, b, c, d, or X. A “-” character between two other characters forms a range that matches all characters from the first character to the second.

[.characters.]: Within a bracket expression (using “[” and “]”), matches the sequence of characters of that collating element. i.e. the pattern [[.period.]] would match the ‘.’ (period) character.

[=character_class=] : Within a bracket expression, represents an equivalence class. It matches all characters with the same collation value, including itself.

[:character_class:]: Within a bracket expression, represents a character class that matches all characters belonging to that class. i.e. the pattern [[:alpha:]] would match against a string that is all alphabetic characters.

[[:<:]],[[:>:]]: These markers stand for word boundaries, and as such, they match the beginning and ending of words, respectively.