MySql FIND_IN_SET and MySql Locate

The previous lesson of MySql Lessons Series was about MySql Sum Function and in this lesson we will learn about LOCATE and FIND_IN_SET Functions in MySql.

MySql FIND_IN_SET()

MySql FIND_IN_SET Function is used to find a string in a list of comma separated strings and returns the position of the string if present. If not present, the function returns 0. The basic structure of MySql FIND_IN_SET function is given below:

SELECT ColumnNames FROM Table WHERE 1 AND 
FIND_IN_SET(String,ColumnName)

For better understanding of MySql FIND_IN_SET function, consider the table shown in the image below:



You can see the above table 'Geo_Record' contains the names of a few countries and their respective comma separated cites. Let us say we want to find if the comma separated cities against the country 'USA' contains 'NewYork' or not using FIND_IN_SET function i.e

SELECT * FROM `Geo_Record` WHERE Country='USA' AND 
FIND_IN_SET('NewYork',Cities)

The output of the above query will return a row from table 'Geo_Record' with Country 'USA', because the comma separated Cities against 'USA' contains 'NewYork'.

MySql Locate()

MySql Locate Function is used to find the position of a string in another string and return the position if present. If not present, the function returns 0. The basic structure of MySql LOCATE function is given below:

SELECT ColumnNames FROM Table WHERE 1 AND 
LOCATE(String,ColumnName)

Consider the same table we used above for MySql FIND_IN_SET i.e



If we want to find out whether the string in the column 'Cities' against the Country 'USA' contains 'NewYork', then this is how we can do it using MySql Locate function:

SELECT * FROM `Geo_Record` WHERE Country='USA' AND 
LOCATE('NewYork',Cities)

The output of the above query will return a row from table 'Geo_Record' with Country 'USA', because the string in the Cities column against 'USA' contains 'NewYork'.

Difference between FIND_IN_SET and LOCATE

The results of both MySql FIND_IN_SET and MySql Locate was same in the above examples. But there is a little difference between the two. In the example of MySql FIND_IN_SET the cities in the 'Cities' column were treated as a list of cities separated by commas while in MySql Locate the cities in the 'Cities' column were not treated as separate cities but a single string.

Let us say we want to find the string 'NewY' in the Cities column using both techniques. i.e

SELECT * FROM `Geo_Record` WHERE Country='USA' AND 
FIND_IN_SET('NewY',Cities)

and

SELECT * FROM `Geo_Record` WHERE Country='USA' AND 
LOCATE('NewY',Cities)

In the above queries, MySql Locate will return a row with Country 'USA' because the Cities column is treated as a single string and the position of the string 'NewY' can be found in it but on the other hand MySql FIND_IN_SET will return 0 rows because the string 'NewY' does not match any comma separated city exactly.

Related Posts
CASE in MySQl
MySql IF Statement
MySql Replace Function
MySql CONCAT() Function