MySQL FIND_IN_SET() String Function

MySQL FIND_IN_SET() String Function

The MySQL FIND_IN_SET() string function returns the position of a string within a list of strings separated by commas. This function is essential for finding the position of a string in a comma-separated list in SQL queries.

Syntax

SELECT FIND_IN_SET(string, list) AS result
FROM table_name;

The FIND_IN_SET() function has the following components:

  • string: The string to be searched for within the list.
  • list: A comma-separated list of strings in which to search for the specified string.
  • result: An alias for the resulting index position.
  • table_name: The name of the table from which to retrieve the data.

Example MySQL FIND_IN_SET() String Function

Let's look at some examples of the MySQL FIND_IN_SET() string function:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE color_lists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    color_list VARCHAR(255) NOT NULL
);

This query creates a table named color_lists with columns for id and color_list.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO color_lists (color_list)
VALUES ('Red,Green,Blue,Yellow,Purple'),
       ('Cyan,Magenta,Yellow,Black'),
       ('Orange,Pink,Brown,Gray');

This query inserts three rows into the color_lists table.

MySQL INSERT INTO TABLE

Step 4: Using FIND_IN_SET() with WHERE Clause

Use the FIND_IN_SET() function to find the position of a string within a comma-separated list:

SELECT color_list, FIND_IN_SET('Blue', color_list) AS position
FROM color_lists;

This query retrieves the color_list column from the color_lists table and returns the position of 'Blue' within the list.

MySQL FIND_IN_SET() WITH WHERE CLAUSE

Step 5: Using FIND_IN_SET() with Multiple Columns

Use the FIND_IN_SET() function with multiple columns:

SELECT id, color_list, FIND_IN_SET('Yellow', color_list) AS position
FROM color_lists;

This query retrieves the id and color_list columns from the color_lists table and returns the position of 'Yellow' within the list.

MySQL FIND_IN_SET() WITH MULTIPLE COLUMNS

Step 6: Using FIND_IN_SET() with Constants

Use the FIND_IN_SET() function with constants:

SELECT FIND_IN_SET('Gray', 'Red,Green,Blue,Yellow,Purple,Gray') AS gray_position;

This query retrieves the position of the constant string 'Gray' within the specified comma-separated list of colors.

MySQL FIND_IN_SET() WITH CONSTANTS

Conclusion

The MySQL FIND_IN_SET() function is a powerful tool for finding the position of a string in a comma-separated list in SQL queries. Understanding how to use the FIND_IN_SET() function is essential for effective data querying and analysis in MySQL.