Using Find_in_Set in MySQL

MySQL has several ways to locate a substring, and FIND_IN_SET is the right pick when your data is stored as a comma-separated list in a single column. It returns the 1-based index of the searched value inside the list, or 0 when it is not present. That makes it safe to use in a WHERE clause because any non-zero return is truthy.

Create a sample table:

CREATE TABLE CITIES (
    ID INT NOT NULL AUTO_INCREMENT,
    CITY VARCHAR(25) NOT NULL,
    COUNTRY VARCHAR(25) NOT NULL,
    CONTINENT VARCHAR(25) NOT NULL,
    PRIMARY KEY (ID)
);

Populate it and query for every city on a continent:

INSERT INTO CITIES (CITY, COUNTRY, CONTINENT) VALUES
    ('Athens',       'Greece',        'Europe'),
    ('New York',     'USA',           'North America'),
    ('Barcelona',    'Spain',         'Europe'),
    ('Tokyo',        'Japan',         'Asia'),
    ('Mumbai',       'India',         'Asia'),
    ('Sydney',       'Australia',     'Australia'),
    ('Cape Town',    'South Africa',  'Africa'),
    ('Buenos Aires', 'Argentina',     'South America'),
    ('Kabul',        'Afghanistan',   'Asia'),
    ('Ottawa',       'Canada',        'North America');

SELECT CITY, COUNTRY
FROM   CITIES
WHERE  FIND_IN_SET('Asia', CONTINENT);

Output:

+--------+-------------+
| CITY   | COUNTRY     |
+--------+-------------+
| Tokyo  | Japan       |
| Mumbai | India       |
| Kabul  | Afghanistan |
+--------+-------------+

2026 caveat: do not use this for new schemas

If you find yourself reaching for FIND_IN_SET against data you control, that is a signal to normalize. Comma-separated lists cannot be indexed, so every query does a full table scan. Either split the data into a proper join table or switch to a native JSON column with a generated index. FIND_IN_SET is most valuable today as a rescue tool for legacy schemas that cannot be reshaped.

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.