FIND_IN_SET() Function in MySql

How to use FIND_IN_SET() function in MySql . This tutorial explain how to use this in-built function to search for values within a comma separated values.

While working on some module i came across with MySql FIND_IN_SET() function. In my case i have to pull all the records whose status match with status list separated with comma are stored in table.

Syntax of FIND_IN_SET() function

1	FIND_IN_SET(argument1,argument2)

argument1 is a string.

argument2 is a string list separated by comma.

find_in_set() function returns the position of a string within second string. Returns zero when search string doesn’t exist in string list.

Note:

i) If string is not found in string_list, the FIND_IN_SET function will return 0.

ii) If string is NULL, the FIND_IN_SET function will return NULL.

iii) If string_list is an empty string, the FIND_IN_SET function will return 0.

iv) If string_list is NULL, the FIND_IN_SET function will return NULL.

Examples

1 2 3 4

/* Search a in a string of 'b,a,c,d' */ select find_in_set ('a', 'b,a,c,d');   //output - 2

1 2 3 4 5

/* Search h in a string of 'b,a,c,d' */   select find_in_set ('h', 'b,a,c,d');   //output - 0, as h is not present in a list

1 2 3 4 5

/* Search a in NULL value. */   select find_in_set ('a', NULL);   //output - NULL

Explanation of find_in_set() Function through Example

Create one table category and populate some values.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create table category (   category_id int(10) NOT NULL AUTO_INCREMENT,   name varchar(200),   cat_path varchar(200),   PRIMARY KEY(category_id) );   insert into category (name,cat_path) values('Electronics','4,2,6,7');   insert into category (name,cat_path) values('Mobiles','2,4,6');   insert into category (name,cat_path) values('DSLR','1,4,7');   insert into category (name,cat_path) values('Fashion','3,9,2');

Table will look like this.

1 2 3 4 5 6 7 8 9 10

mysql> select * from category; +-------------+-------------+----------+ | category_id | name        | cat_path | +-------------+-------------+----------+ |           1 | Electronics | 4,2,6,7  | |           2 | Mobiles     | 2,4,6    | |           3 | DSLR        | 1,4,7    | |           4 | Fashion     | 3,9,2    | +-------------+-------------+----------+ 4 rows in set (0.00 sec)

Let’s write query to find all the category_id and name whose cat_path contains value 4.

1 2 3 4 5 6 7 8 9 10 11

/* Search category 4 in cat_path (which is a comma separated cat_ids) */ mysql> select * from category where find_in_set('4',cat_path); +-------------+-------------+----------+ | category_id | name        | cat_path | +-------------+-------------+----------+ |           1 | Electronics | 4,2,6,7  | |           2 | Mobiles     | 2,4,6    | |           3 | DSLR        | 1,4,7    | +-------------+-------------+----------+ 3 rows in set (0.00 sec)

Three matching rows are fetched.