MySQL FIND_IN_SET() – Find Substring Positions using MySQL

By /
FIND IN SET Function

In this tutorial, we will learn about the MySQL FIND_IN_SET() function. The FIND_IN_SET() function is used to return the position of a string or a number from a list of strings or numbers. Suppose we have a list of strings and we want to find the position of ‘Hello’ in the string list. If ‘Hello’’ is found in the list, FIND_IN_SET() returns the position of ‘Hello’ in the list otherwise, it returns 0. FIND_IN_SET() performs a case-insensitive search.

Syntax of MySQL FIND_IN_SET()

FIND_IN_SET(value, list);Code language: SQL (Structured Query Language) (sql)

Where ‘value’ is the string or number that needs to be searched and,

‘list’ is the list in which you want to ‘value.

Note:

  • If the ‘value’ is not found in the ‘list’, FIND_IN_SET() returns 0.
  • If the ‘value’ or the ‘list’ is NULL, FIND_IN_SET() returns NULL.
  • If the ‘list’ is empty and you are searching for a ‘value’, FIND_IN_SET() returns 0.

Examples of MySQL FIND_IN_SET()

Let us look at a couple of basic examples. Consider the below query. We use aliases in our SELECT statements to make our output readable.

SELECT FIND_IN_SET('c', 'a,b,c') AS PositionOfC;Code language: SQL (Structured Query Language) (sql)

We are searching for ‘c’ in a list containing ‘a’, ‘b’ and ‘c’. The output is,

Find In Set Basic Example

Now lets us take an example in which we are finding a number in a list of numbers. Consider the below query,

SELECT FIND_IN_SET(84, '2,8,56,32,84,59,30');Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Find In Set Numbers

MySQL FIND_IN_SET() When Value Is Not Present In The List

When the value we want to search for is not present in the list, MySQL FIND_IN_SET() returns 0. We can demonstrate using the below example. Let us try to find ‘k’ in the list ‘a,b,c’.

SELECT FIND_IN_SET('k', 'a,b,c') AS PositionOfK;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Find In Set Not Present

MySQL FIND_IN_SET() Is Case-Insensitive

As I mentioned earlier, FIND_IN_SET() is case-insensitive. That means if your list has a ‘b’ and you want to search ‘B’, it will return the position of ‘b’. We can demonstrate this using the below example.

SELECT FIND_IN_SET('B', 'a,b,c') AS PositionOfB;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows,

Find In Set Case Insensitive

MySQL FIND_IN_SET() Behavior With NULL

If either of the parameters – ‘value’ or ‘list’ is NULL, FIND_IN_SET() returns NULL. Let us see this using the two queries below.

SELECT FIND_IN_SET(NULL, 'a,b,c') AS PositionOfNULL; 
SELECT FIND_IN_SET('a', NULL) AS PositionOfA;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows,

MySQL Find_In_Set Null Example

MySQL FIND_IN_SET() With Tables

Let us now see how we can use FIND_IN_SET with tables. Consider the below ConferenceGuests table.

Conference Guests Table Find In Set
ConferenceGuests Table

As you know, FIND_IN_SET() takes two arguments – ‘value’ and ‘list’. Keeping this in mind, let us write a query using FIND_IN_SET() that displays only those guest records from the ConferenceGuests table who are from Japan. The query for it is as follows,

SELECT ID, Name, Country FROM ConferenceGuests WHERE FIND_IN_SET('Japan', Country);Code language: SQL (Structured Query Language) (sql)

And we get the output as follows:

Find In Set Table Example 1

Here, we take the entire ‘Country’ column as our list and filter to those positions where the value is ‘Japan’. But wait, we get two records in our result-set. Does FIND_IN_SET() return positions of any occurrences of the value? No. So, you must be wondering how did we get two records? Let us take a look at the below query.

SELECT FIND_IN_SET('Japan', Country) FROM ConferenceGuests;Code language: SQL (Structured Query Language) (sql)

And we get the output as:

MySQL Find_In_SET Table Example 1 1

As you can see, this query does not return the position of ‘Japan’ but marks those rows with 1 which contain the value ‘Japan’ while the rest are marked 0. When this output is received in the WHERE clause, it only filters to those records that are marked with the value 1 and displays them.

Now, building on the above example, let us display records of those guests from the ConferenceGuests table who are not from India, Japan or Australia using FIND_IN_SET(). We will need to use the NOT operator. We will use the below query,

SELECT ID, Name, Country FROM ConferenceGuests WHERE NOT FIND_IN_SET(Country, 'India,Japan,Australia');Code language: SQL (Structured Query Language) (sql)

And the output is,

Find In Set Table Example 2

Conclusion

FIND_IN_SET() is a function that you might not use frequently but it does find use in a lot of use cases, especially in a list of numbers. I would encourage you to play around with the function and take a look at the references.

References