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, 2^nd and 4^th 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 10^th 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.