Using Find_in_Set in MySQL

MySQL has various ways to search for or lookup a given string. One such powerful mechanism is by using FIND_IN_SET. This function enables a lookup for a given string in a set (probably comma separated ones).

Example:

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

Also, add records as below, that we will query for all cities that belong to a particular 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 query: SELECT CITY, COUNTRY FROM CITIES WHERE FIND_IN_SET('Asia', CONTINENT); 

Output for the above query

+ -- -- -- -- -- -+ -- -- -- -- -- -- -- -- -- -- +| CITY | COUNTRY |+ -- -- -- -- -- -+ -- -- -- -- -- -- -- -- -- -- +| Tokyo | Japan || Mumbai | India || Kabul | Afghanistan |+ -- -- -- -- -- -+ -- -- -- -- -- -- -- -- -- -- + 
MySQL has various ways to search for or lookup a given string
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

More From DevX