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

Overview

The Latest

Windows Logging is one of the vital aspects of any Windows system administration. However, it is mostly overlooked until the system develops a problem. This is because logs contain important information needed to troubleshoot and resolve system issues.

The Fundamentals of Windows Logging

Windows Logging is one of the vital aspects of any Windows system administration. However, it is mostly overlooked until the system develops a problem. This is because logs contain important information needed to troubleshoot and resolve system issues. Without it, tech experts might find it difficult to track a computer’s

Interstitial Ads: Best Practices for Successful Campaigns

Interstitial Ads: Best Practices for Successful Campaigns

Interstitial Ads: Best Practices for Successful Campaigns Interstitial ads are full-screen advertisements that appear to grasp the attention of on-site prospects, creating opportunities for brands seeking effective ways to communicate their proposition of value. With such an attention-grabbing format and high-impact visuals, it’s no wonder why interstitial advertising is proving

positive contribution tech

Technology’s Positive Contributions to Society

Technology has and continues to improve our lives. From the business world to the world of medicine, and our day-to-day lives, you can’t go a day without interacting with at least one form of technology. While some fear technology may be going too far, there are many ways in which

How to Choose From The Best Big Data Platforms in 2023

How to Choose From The Best Big Data Platforms in 2023

As big data continues to become increasingly popular in the business world, companies are always looking for better ways to process and analyze complex data. The process critically depends on the platform that manages and analyzes the data. In this article, we will provide a guide to help you choose

Why transparent code is a good idea

Why Transparent Code is a Good Idea

Code is used to make up the apps and software we use every day. From our favorite social media platforms to our online banking services, code is the framework used to build these tools that help make our lives easier. Code is complex. Software today requires large teams of programmers