find_in_set() function in MySQL
This Function is used for finding the position of a particular string from the list of strings. For suppose if the specified string is repeated multiple times, then this functions outputs the first occurrence of that specified string.
We provide the specified string and the list of strings to the function as the parameters.
- If the specified string is absent in the list of strings, then this function returns 0.
- If the specified string is NULL, then this function will result a NULL value.
- If the List of strings is NULL or an empty string, then this function will return 0.
Syntax:
FIND_IN_SET (“String”, “List_of_Strings”)
Parameters:
- String: This is the string which should be searched in the list of strings.
- List_of_Strings: This is the strings list from which the searching is done.
Example1:
Search for a “e” from the Strings list.
select FIND_IN_SET (“e”, “j, a, v, a, T, p, o, i, n, t”);
Output:
FIND_IN_SET (“e”, “javaTpoint”);
0
As the letter “e” is not present in the list of strings, this function will give 0 as output.
Example2:
Now, we will search for “T” in “javaTpoint”.
select FIND_IN_SET (“T”, “j, a, v, a, T, p, o, i, n, t”);
Output:
FIND_IN_SET (“T”, “javaTpoint”);
5
As the letter “T” is present in the list of strings at the position 5, this function will give 5 as output.
Example3:
Now, let’s search for “a” in “javaTpoint”.
select FIND_IN_SET (“a”, “j, a, v, a, T, p, o, i, n, t”);
Output:
FIND_IN_SET (“a”, “javaTpoint”);
2
As letter “a” is present twice in the list of strings (i.e “javaTpoint”), this function will give 2 as output. It gives the first occurrence as the output. In “javaTpoint”, the letter “a” is present in two positions, 2nd and 4th position. So, we got the output as 2.
Example4:
select FIND_IN_SET (“z”, null);
FIND_IN_SET (“z”, null);
null
Example5:
select FIND_IN_SET (“t”, “j, a, v, a, T, p, o, i, n, t);
Output:
FIND_IN_SET (“t”, “j, a, v, a, T, p, o, i, n, t);
10
As “t” is at 10th position, this function results 10 as the output.
Example6:
FIND_IN_SET () Function using where clause.
Consider a Table AUTHOR having ID, Name, country as the fields.
create table AUTHOR (ID number, Name varchar (20), country varchar (25));
>Table Created.
After inserting rows into the table, we are having the following table.
select * from AUTHOR;
ID | Name | Country |
AUT011 | William Anthony | Germany |
AUT012 | Thomas Morgan | UK |
AUT013 | Nikolai Dewey | Canada |
AUT014 | William Norton | Brazil |
AUT015 | William Maugham | USA |
AUT016 | Joseph Milton | Canada |
AUTO17 | Evan Hayek | Australia |
AUTO18 | Joseph Dewey | USA |
AUTO19 | William Morgan | Australia |
>9 rows selected.
Now, if we execute the following query,
select * from author where FIND_IN_SET (left (Name,7), “William, steven, angalo, Jung”) > 0;
Output:
ID | Name | Country |
AUT011 | William Anthony | Germany |
AUT014 | William Norton | Brazil |
AUT015 | William Maugham | USA |
AUTO19 | William Morgan | Australia |
The above query will give the rows having author name Starting with “William”.
In this way we can use the FIND_IN_SET Function in where clause also. In this way we use the FIND_IN_SET Function in the database applications.
Let’s look into some examples using tables from the database.
create table if not exists Divisions (id number AUTO_INCREMENT primary key, name varchar (20) not null, belts varchar (20));
Now, we should insert some rows into the table Divisions.
insert into Division values (‘0-1’, ‘yellow, red, purple’);
insert into Division values (‘0-2’, ‘brown, black, brown’);
insert into Division values (‘0-3’, ‘white, green, purple’);
insert into Division values (‘0-4’, ‘blue, yellow, pink’);
insert into Division values (‘0-5’, ‘blue, red, pink’);
insert into Division values (‘0-6’, ‘red, yellow, pink’);
insert into Division values (‘0-7’, ‘blue, yellow, red’);
insert into Division values (‘0-8’, ‘blue, pink, purple’);
insert into Division values (‘0-9’, ‘green, blue, pink’);
insert into Division values (‘0-10’, ‘blue, purple, pink’);
select * from Divisions;
ID | Name | Belts |
1 | 0-1 | Yellow, red, purple |
2 | 0-2 | brown, black, brown |
3 | 0-3 | White, green, purple |
4 | 0-4 | Blue, yellow, pink |
5 | 0-5 | blue, red, pink |
6 | 0-6 | red, yellow, pink |
7 | 0-7 | blue, yellow, red |
8 | 0-8 | blue, pink, purple |
9 | 0-9 | green, blue, pink |
10 | 0-10 | blue, purple, pink |
>10 rows selected.
To, find a particular division having a red belt, we use Find_in_Set() Function in the query as follows:
select id, name, belts from Divisions where find_in_set (‘purple’, belts);
Output:
ID | Name | Belts |
1 | 0-1 | Yellow, red, purple |
3 | 0-3 | White, green, purple |
8 | 0-8 | blue, pink, purple |
Negative Find_In_Set() Function:
To, print the argument which is not in the second argument, we should use NOT operator. Consider the following query,
select id, name, belts from Divisions where NOT FIND_IN_SET (‘white’, belts);
Output:
ID | Name | Belts |
1 | 0-1 | Yellow, red, purple |
2 | 0-2 | Brown, black, brown |
4 | 0-4 | Blue, yellow, pink |
5 | 0-5 | blue, red, pink |
6 | 0-6 | red, yellow, pink |
7 | 0-7 | blue, yellow, red |
8 | 0-8 | blue, pink, purple |
9 | 0-9 | green, blue, pink |
10 | 0-10 | blue, purple, pink |
FIND_IN_SET () Function vs. IN operator:
The IN operator works whether a value matched in a set or not. Now, we will look into an example having IN operator in the query,
select id, name, belts from Divisions where name IN (‘0-1’, ‘0-3’);
ID | Name | Belts |
1 | 0-1 | Yellow, red, purple |
2 | 0-3 | White, green, purple |
The result which is produced in the above query, can be obtained using FIND_IN_SET () Function also. For that we write the below query,
select id, name, belts from Division where FIND_IN_SET (name, ‘0-1, 0-3’);
ID | Name | Belts |
1 | 0-1 | Yellow, red, purple |
2 | 0-3 | White, green, purple |
The IN operator can take many arguments with in the function whereas the Find_In_Set () Function can only take two arguments.
Generally, we use the IN operator if we want to match a value with any value from the provided list.
We use Find_In_Set () Function to match the given value with the comma separated list of values.
In this way we can differentiate the Find In Set Function and IN operator.