TechBeamersTechBeamers
  • Learn ProgrammingLearn Programming
    • Python Programming
      • Python Basic
      • Python OOP
      • Python Pandas
      • Python PIP
      • Python Advanced
      • Python Selenium
    • Python Examples
    • Selenium Tutorials
      • Selenium with Java
      • Selenium with Python
    • Software Testing Tutorials
    • Java Programming
      • Java Basic
      • Java Flow Control
      • Java OOP
    • C Programming
    • Linux Commands
    • MySQL Commands
    • Agile in Software
    • AngularJS Guides
    • Android Tutorials
  • Interview PrepInterview Prep
    • SQL Interview Questions
    • Testing Interview Q&A
    • Python Interview Q&A
    • Selenium Interview Q&A
    • C Sharp Interview Q&A
    • PHP Interview Questions
    • Java Interview Questions
    • Web Development Q&A
  • Self AssessmentSelf Assessment
    • Python Test
    • Java Online Test
    • Selenium Quiz
    • Testing Quiz
    • HTML CSS Quiz
    • Shell Script Test
    • C/C++ Coding Test
Search
  • Python Multiline String
  • Python Multiline Comment
  • Python Iterate String
  • Python Dictionary
  • Python Lists
  • Python List Contains
  • Page Object Model
  • TestNG Annotations
  • Python Function Quiz
  • Python String Quiz
  • Python OOP Test
  • Java Spring Test
  • Java Collection Quiz
  • JavaScript Skill Test
  • Selenium Skill Test
  • Selenium Python Quiz
  • Shell Scripting Test
  • Latest Python Q&A
  • CSharp Coding Q&A
  • SQL Query Question
  • Top Selenium Q&A
  • Top QA Questions
  • Latest Testing Q&A
  • REST API Questions
  • Linux Interview Q&A
  • Shell Script Questions
© 2024 TechBeamers. All Rights Reserved.
Reading: MySQL FIND_IN_SET Function with Simple Examples
Font ResizerAa
TechBeamersTechBeamers
Font ResizerAa
  • Python
  • SQL
  • C
  • Java
  • Testing
  • Selenium
  • Agile Concepts Simplified
  • Linux
  • MySQL
  • Python Quizzes
  • Java Quiz
  • Testing Quiz
  • Shell Script Quiz
  • WebDev Interview
  • Python Basic
  • Python Examples
  • Python Advanced
  • Python OOP
  • Python Selenium
  • General Tech
Search
  • Programming Tutorials
    • Python Tutorial
    • Python Examples
    • Java Tutorial
    • C Tutorial
    • MySQL Tutorial
    • Selenium Tutorial
    • Testing Tutorial
  • Top Interview Q&A
    • SQL Interview
    • Web Dev Interview
  • Best Coding Quiz
    • Python Quizzes
    • Java Quiz
    • Testing Quiz
    • ShellScript Quiz
Follow US
© 2024 TechBeamers. All Rights Reserved.
MySQL Tutorial

MySQL FIND_IN_SET Function with Simple Examples

Last updated: Sep 28, 2023 8:35 pm
By Meenakshi Agarwal
Share
8 Min Read
MySQL FIND_IN_SET function with examples
MySQL FIND_IN_SET function with examples
SHARE

This tutorial explains MySQL FIND_IN_SET function which finds the position of a string within a sequence of strings separated by commas (i.e., comma-separated such as str1, str2, find_this_string,…) with examples.

Contents
SyntaxFIND_IN_SET Flow DiagramUsing MySQL FIND_IN_SET() with ExamplesI- Using FIND_IN_SET() without a tableII- Using FIND_IN_SET() with tablesIII- Using Not operator with FIND_IN_SET()Difference between MySQL FIND_IN_SET() & IN operator

1. FIND_IN_SET() Syntax
2. FIND_IN_SET Flow Diagram
3. FIND_IN_SET() Examples
4. FIND_IN_SET() Function Vs. IN operator

Let’s now go through each of the sections one by one.

MySQL FIND_IN_SET() Function

As stated initially, FIND_IN_SET() is a built-in MySQL function that returns the index of a string in a list of comma-delimited strings. So, let’s now see the details and check out how can we use it.

Syntax

Below is the signature of this method:

FIND_IN_SET(String_to_Find, List_of_Strings);

Below are the descriptions of the parameters in the above function.

+-----------------+-------------------------------------+
| # Parameters    | # Description                       |
+-----------------+-------------------------------------+
| String_to_Find  | Target string that you like to find.|
| List_of_Strings | String list that holds your target. |
+-----------------+-------------------------------------+

After looking at the arguments, let’s check out what does the FIND_IN_SET() function returns.

  • If either of the two parameters (String_to_Find or List_of_Strings) is NULL, it will return a NULL.
  • When you pass a string that is not in the list, or it’s empty, then FIND_IN_SET() returns zero.
  • It is the happy path case when the string you passed is available. In this case, the return value is the position of String_to_Find.

Now, let’s talk about some exceptional cases. The first of such scenarios is when the String_to_Find carries a comma character. If so, then the function may not work as expected.

Another case is where the String_to_Find happens to be a constant and List_of_Strings is of SET type. MySQL handles such a scenario by doing the bitwise operation.

FIND_IN_SET Flow Diagram

Below is a simple visualization of the FIND_IN_SET function.

MySQL FIND_IN_SET Flow Diagram

Next, we have brought several examples to demonstrate the use of the MySQL FIND_IN_SET() function. It will surely help you understand and use it to write better queries.

Using MySQL FIND_IN_SET() with Examples

Let’s begin with the bare use of the FIND_IN_SET() function first.

I- Using FIND_IN_SET() without a table

1) Find the position of a multi-char string in a list of strings

mysql> SELECT FIND_IN_SET('MySQL', 'Python,CSharp,C,PHP,MySQL');

The output:

5

2) Find the position of a char in a list of chars. Here, we are searching for a vowel in the list of vowels.

mysql> SELECT FIND_IN_SET('o','a,e,i,o,u');

The output:

4

3) We kept the 2nd argument empty. Hence, the function returns a zero.

mysql> SELECT FIND_IN_SET('X', '');

The output:

0

4) Let’s keep the first argument NULL. Hence, the function also returns a NULL.

mysql> SELECT FIND_IN_SET(NULL, 'A,B,C');

The output:

NULL

5) If we keep the second argument NULL, then also, the function returns NULL.

mysql> SELECT FIND_IN_SET('X', NULL);

The output:

NULL

Now, we’ll explore FIND_IN_SET() on tables.

II- Using FIND_IN_SET() with tables

In this MySQL FIND_IN_SET example, we’ll first create a table named Players. Run the following in your SQL editor:

CREATE TABLE IF NOT EXISTS Players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Sport_name VARCHAR(15) NOT NULL,
    Player_names VARCHAR(100) NOT NULL
);

After finishing with the SQL table creation, we’ve to populate the data into it. For this, we need to run the below command.

INSERT INTO Players (Sport_name, Player_names)
VALUES ('Hockey','Jimmy,Carol,Rami'),
 ('Soccer','Stokes,Joe,Ben'),
 ('Cricket','Kane,Henry,Ross'),
 ('Tennis','Jimmy,Carol,Rami'),
 ('Volleyball','Stokes,Joe,Ben'),
 ('Basketball','Kane,Henry'),
 ('Baseball','Ross'),
 ('Rugby','Jimmy,Carol,Rami'),
 ('Golf','Stokes,Joe,Ben'),
 ('Boxing','Kane,Henry');

The above is the Players vs. Sports table which stores the list of players who play multiple sports. After getting the data in the table, you can check the same by running the “SELECT * from Players” command.

Finally, we can now run our use case. In this, we expect to find out the sports that “Kane” plays using the FIND_IN_SET(). Check out the following query:

SELECT 
    Sport_name, 
    Player_names
FROM
    Players
WHERE
    FIND_IN_SET('Kane', Player_names);

After executing the above, we see the below result:

+------------+-----------------+
| Sports     | Players         |
+------------+-----------------+
| Cricket    | Kane,Henry,Ross |
| Basketball | Kane,Henry      |
| Boxing     | Kane,Henry      |
+------------+-----------------+

III- Using Not operator with FIND_IN_SET()

MySQL treats zero as FALSE. And the FIND_IN_SET() function’s output is also zero when it fails to find the string. So, its result is interpreted as FALSE.

Hence, it also means that we can club the NOT operator with this function to find odd rows.

The below example finds those Sports in which Ross doesn’t take part. We’ll use the NOT operator along with the FIND_IN_SET().

SELECT 
    Sport_name, Player_names
FROM
    Players
WHERE
    NOT FIND_IN_SET('Ross', Player_names);

After running the above MySQL query, you get this result:

+------------+------------------+
| Sports     | Players          |
+------------+------------------+
| Hockey     | Jimmy,Carol,Rami |
| Soccer     | Stokes,Joe,Ben   |
| Tennis     | Jimmy,Carol,Rami |
| Volleyball | Stokes,Joe,Ben   |
| Basketball | Kane,Henry       |
| Rugby      | Jimmy,Carol,Rami |
| Golf       | Stokes,Joe,Ben   |
| Boxing     | Kane,Henry       |
+------------+------------------+

Also Read: MySQL ABS Function with Simple Examples

Difference between MySQL FIND_IN_SET() & IN operator

MySQL also supports the age-old IN operator that also looks for a value in the set.

Check out the below example which uses the IN operator to list players who play both Hockey as well as Soccer.

The SELECT query will look like:

SELECT 
    Sport_name, Player_names
FROM
    Players
WHERE
    Sport_name IN ('Hockey' , 'Soccer');

And, after running this MySQL command, the result is:

+--------+------------------+
| Sports | Players          |
+--------+------------------+
| Hockey | Jimmy,Carol,Rami |
| Soccer | Stokes,Joe,Ben   |
+--------+------------------+

However, we should get the same result using the FIND_IN_SET(). Let’s write a query for the same.

SELECT 
    Sport_name, Player_names
FROM
    Players
WHERE
    FIND_IN_SET(Sport_name, 'Hockey,Soccer');

You can now observe the similarity between the IN and FIND_IN_SET() expressions. The condition in the WHERE clause is almost the same.

Therefore, let’s list down the differences between IN and FIND_IN_SET():

  • The IN operator can take variable no. of values in the set, whereas the FIND_IN_SET() accepts only two.
  • With IN, we can match a value with any item in a list, whereas FIND_IN_SET() matches only in a comma-delimited list.

We hope that after reading this tutorial, you should feel comfortable using the MySQL FIND_IN_SET() function. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, read our step-by-step MySQL tutorial.

You Might Also Like

MySQL vs MongoDB Comparison

Concatenate Strings in an SQL Query With Examples

The Difference between UPSERT & Insert

SQL Programming Test in 2024

SQL Table Creation: The Missing Manual

Meenakshi Agarwal Avatar
By Meenakshi Agarwal
Follow:
Hi, I'm Meenakshi Agarwal. I have a Bachelor's degree in Computer Science and a Master's degree in Computer Applications. After spending over a decade in large MNCs, I gained extensive experience in programming, coding, software development, testing, and automation. Now, I share my knowledge through tutorials, quizzes, and interview questions on Python, Java, Selenium, SQL, and C# on my blog, TechBeamers.com.
Previous Article MySQL Declare Variable local, user-defined, system How to Declare Variables in MySQL
Next Article MySQL DATE_SUB Function with Examples MySQL DATE_SUB Function with Simple Examples

Popular Tutorials

SQL Interview Questions List
50 SQL Practice Questions for Good Results in Interview
SQL Interview Nov 01, 2016
Demo Websites You Need to Practice Selenium
7 Sites to Practice Selenium for Free in 2024
Selenium Tutorial Feb 08, 2016
SQL Exercises with Sample Table and Demo Data
SQL Exercises – Complex Queries
SQL Interview May 10, 2020
Java Coding Questions for Software Testers
15 Java Coding Questions for Testers
Selenium Tutorial Jun 17, 2016
30 Quick Python Programming Questions On List, Tuple & Dictionary
30 Python Programming Questions On List, Tuple, and Dictionary
Python Basic Python Tutorials Oct 07, 2016
//
Our tutorials are written by real people who’ve put in the time to research and test thoroughly. Whether you’re a beginner or a pro, our tutorials will guide you through everything you need to learn a programming language.

Top Coding Tips

  • PYTHON TIPS
  • PANDAS TIPSNew
  • DATA ANALYSIS TIPS
  • SELENIUM TIPS
  • C CODING TIPS
  • GDB DEBUG TIPS
  • SQL TIPS & TRICKS

Top Tutorials

  • PYTHON TUTORIAL FOR BEGINNERS
  • SELENIUM WEBDRIVER TUTORIAL
  • SELENIUM PYTHON TUTORIAL
  • SELENIUM DEMO WEBSITESHot
  • TESTNG TUTORIALS FOR BEGINNERS
  • PYTHON MULTITHREADING TUTORIAL
  • JAVA MULTITHREADING TUTORIAL

Sign Up for Our Newsletter

Subscribe to our newsletter to get our newest articles instantly!

Loading
TechBeamersTechBeamers
Follow US
© 2024 TechBeamers. All Rights Reserved.
  • About
  • Contact
  • Disclaimer
  • Privacy Policy
  • Terms of Use
TechBeamers Newsletter - Subscribe for Latest Updates
Join Us!

Subscribe to our newsletter and never miss the latest tech tutorials, quizzes, and tips.

Loading
Zero spam, Unsubscribe at any time.
x