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.
How to compare NULL value in MySql .
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.
How to find second highest salary in MySql.
How to install MySql workbench on Ubuntu.
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.
