dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Apr 25, 2019

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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
M S Sridhar
 
Thanks for your registration, follow us on our social networks to keep up-to-date