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


advertisement
 

Tip: Using Find_in_Set in MySQL

Learn how to search for, or look up, a given string.


advertisement

WEBINAR:

On-Demand

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


MySQL has various ways to search for, or look up, 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 |
+ -- -- -- -- -- -+ -- -- -- -- -- -- -- -- -- -- + 

 

Visit the DevX Tip Bank

 





   
Sridhar M S. is a Java developer from Bangalore, India. He holds a master's degree in Computer Science.
Thanks for your registration, follow us on our social networks to keep up-to-date