FIND_IN_SET Function in SQL
The FIND_IN_SET function of Structured Query Language shows the position of the searched string in the multiple strings.
Syntax of FIND_IN_SET String Function
Syntax1: This syntax uses the FIND_IN_SET function with two or more column names of the SQL table:
In Find_In_Set syntax, we have to specify the name of those columns in which we want to search a string.
Syntax2: This syntax uses the FIND_IN_SET function with the string:
Syntax3: This syntax uses the FIND_IN_SET function with the individual character:
Examples of FIND_IN_SET String function
Example 1: The below query shows the position of the ‘S’ character in the given list of characters:
Output:
S_Findinset |
6 |
Example 2: The below statement shows the position of the ‘u’ character in the Congratulations string:
SELECT FIND_IN_SET( ‘u’, ”C, o, n, g, r, a, t, u, l, a, t, i, o, n, s ”) AS Wishes u_Findinset;
Output:
S_Findinset |
8 |
Example 3: The following SELECT query finds the position of the ‘New’ string in the list of strings:
Output:
NEW_Findinset |
1 |
Example 4: This example uses the FIND_IN_SET function with the table in Structured Query Language.
In this example, we have to create a new SQL table through which we will execute the FIND_IN_SET() function on columns.
The syntax for creating the new table in the SQL database is as follows:
The following CREATE statement creates the Student_Grade table:
The below INSERT queries insert the records of students with grades and marks in the Student_Grade table:
The following SELECT statement displays the inserted records of the above Student_Grade table:
Roll_No | First_Name | Last_Name | First_City | Second_City | New_City | Hindi_Marks | Maths_Marks | Grade |
---|---|---|---|---|---|---|---|---|
10 | Aman | Sharma | Lucknow | Chandigarh | Ghaziabad | 88 | 95 | A2 |
02 | Vishal | Sharma | Chandigarh | Ghaziabad | Ghaziabad | 95 | 82 | A1 |
07 | Raj | Gupta | Delhi | Ghaziabad | Lucknow | 91 | 95 | A1 |
04 | Yash | Singhania | Ghaziabad | Delhi | Lucknow | 85 | 82 | A2 |
11 | Vinay | Roy | Delhi | Kanpur | Ghaziabad | 95 | 97 | A1 |
16 | Manoj | Gupta | Ghaziabad | Meerut | Chandigarh | 95 | 90 | B1 |
19 | Ram | Gupta | Lucknow | Ghaziabad | Chandigarh | 89 | 95 | A2 |
Query 1: The following SELECT query uses the FIND_IN_SET function with the First_City, Second_City, New_City columns of the above Student_Grade table:
This SQL statement shows the position of Ghaziabad city in the given three columns of the table.
Output:
First_City | Second_City | New_City | Ghaziabad_Findinset |
---|---|---|---|
Lucknow | Chandigarh | Ghaziabad | 3 |
Chandigarh | Ghaziabad | Ghaziabad | 3 |
Delhi | Ghaziabad | Lucknow | 2 |
Ghaziabad | Delhi | Lucknow | 1 |
Delhi | Kanpur | Ghaziabad | 3 |
Ghaziabad | Meerut | Chandigarh | 1 |
Lucknow | Ghaziabad | Chandigarh | 2 |
Query 2: The following SELECT query uses the FIND_IN_SET function with the Hindi_Marks and Maths_Marks column of those Students whose Roll_No is greater than 2 in the above Student_Grade table:
Output:
Roll_No | Hindi_Marks | Maths_Marks | 95_Findinset |
---|---|---|---|
07 | 91 | 95 | 2 |
04 | 85 | 82 | Null |
11 | 95 | 97 | 1 |
16 | 95 | 90 | 1 |
19 | 89 | 95 | 2 |