How to split and search in comma-separated string in MySQL

May 3, 2017May 23, 2018 Michael Ryvkin

There could be times when you need to split comma-separated values in a string and get a value at certain position. Obviously this could be done with explode in PHP or split in Perl using server-side script but what if you need to do it directly in MySQL.

Split comma-separated values and retrieve a value at certain position

Surprisingly MySQL doesn’t have a dedicated function for this operations as opposed to split_part in PostgreSQL. Luckily it has SUBSTRING_INDEX() function that does almost what we need.

From the official documentation:

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'
This function is multibyte safe.

Definitions

CREATE TABLE employees (
   id SERIAL,
   name VARCHAR(255),
   address TEXT,
   PRIMARY KEY (id)
);

INSERT INTO employees (id, name, address) VALUES 
(1, 'John Doe', '4225 Collins Street,Apt. A,Erie,PA,16510'),
(2, 'Bob Smith', '234 Main Street,,Erie,PA,16512');

Solution

Suppose we want to find all employees working in a certain state (for example, PA) which is a fourth field in a column holding address as comma-separated values.

With SUBSTRING_INDEX(address, ',', 4) function we will be able to extract everything up to the fourth column containing state

SELECT SUBSTRING_INDEX(address, ',', 4) FROM employees;
+-----------+------------------------------------+
| name      | SUBSTRING_INDEX(address, ',', 4)   |
+-----------+------------------------------------+
| John Doe  | 4225 Collins Street,Apt. A,Erie,PA | 
| Bob Smith | 234 Main Street,,Erie,PA           | 
+-----------+------------------------------------+

Now we know that state would always be the last field in the resulting value. Because SUBSTRING_INDEX() function allows to use negative values to extract fields counting from the right, we will use this ability to extract the rightmost field containing state abbreviation.

SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 4), ',', -1) AS state FROM employees;
+-----------+-------+
| name      | state |
+-----------+-------+
| John Doe  | PA    | 
| Bob Smith | PA    | 
+-----------+-------+

Final solution to get all employees working in a certain state is shown below.

SELECT name FROM employees WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 4), ',', -1)='PA';
+-----------+
| name      |
+-----------+
| John Doe  | 
| Bob Smith | 
+-----------+

Caveats

One thing to be aware about SUBSTRING_INDEX() is that the function returns the whole string if the requested field doesn’t exist.

For example, SUBSTRING_INDEX(address, ',', 6) returns full address.

SELECT name, SUBSTRING_INDEX(address, ',', 6) FROM employees;
+-----------+------------------------------------------+
| name      | SUBSTRING_INDEX(address, ',', 6)         |
+-----------+------------------------------------------+
| John Doe  | 4225 Collins Street,Apt. A,Erie,PA,16510 | 
| Bob Smith | 234 Main Street,,Erie,PA,16510           | 
+-----------+------------------------------------------+

Then attempt to extract sixth field with SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 6), ',', -1) will give us zip code (fifth field) which is wrong.

SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 6), ',', -1) AS country FROM employees;
+-----------+---------+
| name      | country |
+-----------+---------+
| John Doe  | 16510   | 
| Bob Smith | 16510   | 
+-----------+---------+

One possible workaround to this problem would be to check if the number of fields is greater than or equal to requested index and then the code the above, or return empty string, if the field index is invalid.

SELECT 
   name, 
   IF(
      LENGTH(address) - LENGTH(REPLACE(address, ',', '')) + 1 >= 6,
      SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 6), ',', -1),
      ''
   ) AS country 
FROM employees;
+-----------+---------+
| name      | country |
+-----------+---------+
| John Doe  |         | 
| Bob Smith |         | 
+-----------+---------+

Possible uses

In most cases it is better to use a separate lookup table or a column for each field instead of storing data as comma-separated values for later lookup.

However, there could be rare cases where the trick above could be useful. For example, recently I needed to return a list of values from user-defined function and the only way to do it would be to return a string containing comma-separated values. I ended up using SUBSTRING_INDEX() to order the results by one of the fields.

Search in a column containing comma-separated values

MySQL has a dedicated function FIND_IN_SET() that returns field index if the value is found in a string containing comma-separated values.

For example, the following statement returns one-based index of value C in string A,B,C,D.

SELECT FIND_IN_SET('C', 'A,B,C,D') AS result;
+--------+
| result |
+--------+
|      3 | 
+--------+

If the given value is not found, FIND_IN_SET() function returns 0.

SELECT FIND_IN_SET('Z', 'A,B,C,D') AS result;
+--------+
| result |
+--------+
|      0 | 
+--------+

Michael Ryvkin

Freelance web developer based in Perkasie, Bucks County, PA. I design and develop custom websites and web applications, perform website maintenance and provide technical support for small and medium-sized businesses.

Contact me for a free quote or consultation on your project.

Comments

mahammad musthafa says:

May 16, 2017 at 7:41 am

Good Site and Good Examples

Reply

select distinct
  SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4', ',', numbers.n), ',', -1) name
from (
   select @rownum := @rownum + 1 as n
   from YourTable
   cross join (select @rownum := 0) r
) numbers 
order by name

Suresh says:

September 18, 2018 at 9:40 am

Thank you Sunil, This query is really useful for me to get my requirement.

Reply

Ruchi says:

February 21, 2018 at 12:50 am
How to search data in column with comma-separated values?

Suppose I have column with data like:
```
[data]
a,b,c
g,h,k
```
IF i search a,c,h I want SQL to return both rows.
Reply
1. Michael Ryvkin says:
  
  February 21, 2018 at 8:00 am
```
SELECT * FROM table 
WHERE (
   FIND_IN_SET('a', data) != 0 
   OR FIND_IN_SET('c', data) != 0 
   OR FIND_IN_SET('h', data) != 0
);
```
  Reply
tootan says:

May 23, 2018 at 7:00 am

My column value '1,2,3,4,5,6,7'. I am sending '1,4'. How can I match '1,4' with what I have in this column value?

Reply
1. Michael Ryvkin says:
  
  May 23, 2018 at 12:23 pm
  You may need to split the string 1,4 into array containing 1 and 4 using your server-side script. Then construct SQL query using these array elements to search in the database using FIND_IN_SET as shown below.
  
  If you want to find records containing both 1 and 4, use AND; otherwise use OR.
```
SELECT * FROM table
WHERE (
   FIND_IN_SET('1', data) != 0
   AND FIND_IN_SET('4', data) != 0
);
```
  Reply
Udhay says:

November 12, 2018 at 2:28 am

Thanks, good article with examples

Reply
1. Michael Ryvkin says:
  
  November 12, 2018 at 6:13 am
  
  Thank you!
  
  Reply
BBBBB says:

February 21, 2019 at 9:48 am

Hello, I have a question.
I want to find the exact string “1” in this comma-separated string “12,5,3”

As a result, it cannot give me anything because “1” is not present in the string.

How can I do it?

Reply
1. Michael Ryvkin says:
  
  February 21, 2019 at 9:56 am
  
  Not sure I understand. Are you trying to determine whether one string "1" is present in another string "12,5,3" (which it is) or whether string "1" is present in the comma-separated list of values "12,5,3" (which it does not)?
  
  Reply
angelo javier says:

February 26, 2019 at 7:59 pm

If in my table (Table X) I have id | remarks | date as follows
7 | 1000,2000,3000 | 2019-02-27 | such as $remarks="1000,2000,3000".
How do I insert 1000,2000,3000 on a separate table (Table Z) in PHP as
id | A | B | C like 7 | 1000 | 2000 | 3000?
Please help.

Reply
HANY DEV says:

July 11, 2019 at 5:52 pm
Please I need a help. You explain if it was
```
SELECT FIND_IN_SET('C', 'A,B,C,D') AS result;
```
Result will be:
```
| result |
+--------+
|      3 |
+--------+
```
WHAT ABOUT
```
SELECT FIND_IN_SET('A', 'A,B,C,D,A,K,D.A') AS result;
```
IT GIVES ME A RESULT:
```
| result |
+--------+
|      1 |
+--------+
```
BUT IT HAS TO BE:
```
| result |
+--------+
|  1,5,8 |
+--------+
```
HOW IT COME TO GET RESULT AS ARRAY
Reply
1. Michael Ryvkin says:
  
  July 12, 2019 at 2:36 pm
  
  Function FIND_IN_SET() only returns index of the first occurrence, that is why you get 1 as a result.
  
  Reply
Predrag says:

March 6, 2020 at 7:36 am
How to use FIND_IN_SET with NOT? For example:
```
SELECT id FROM table WHERE NOT FIND_IN_SET(id, '1,2,3,4,5');
```
Reply
1. Michael Ryvkin says:
  
  March 6, 2020 at 8:38 am
  From the official documentation, FIND_IN_SET returns 0 if id is not in the comma-separated list or if comma-separated list is an empty string. It also returns NULL if either id or comma-separated list is NULL.
  
  If you are sure id or comma-separated list won’t be equal to NULL, you can use the following statement:
```
SELECT id FROM table WHERE FIND_IN_SET(id, '1,2,3,4,5')=0;
```
  If either id or comma-separated list can be NULL, you can use the following statement:
```
SELECT id FROM table WHERE COALESCE(FIND_IN_SET(id, '1,2,3,4,5'), 0)=0;
```
  Reply
Ahmet tokmak says:

March 19, 2020 at 4:58 pm

Hello , i have column and the values are comma separated example 1,2,3,4,11,23,67 i want to get rows that contains 1 exactly match , i mean if i have row like this 11,34,213,54 and i wanted to search 1 i dont want to get this row. can you help me please i am confused.

Reply
Milan Patel says:

June 11, 2020 at 2:23 am

If in my table (Table X) I have id | remarks | date as follows

7 | 1000,2000,3000 | 2019-02-27 | such as $remarks=”1000,2000,3000″.

How do I insert 1000,2000,3000 on a separate table (Table Z) in PHP as

id | A | B | C like 7 | 1000 | 2000 | 3000?

Please help.

Reply
atsham says:

July 8, 2020 at 6:51 am

$post = DB::table(“addpost”)->whereRaw(‘FIND_IN_SET(?,cate_id)’,$id)->get();

Reply
Hams says:

December 24, 2020 at 6:55 pm
Hi what’s about searching comma separated list in comma separated list
```
SELECT FIND_IN_SET('C,A', 'A,B,C,D') AS result;
```
Reply
Abeeya says:

January 12, 2021 at 6:18 am

how to perform split string and cross apply in mySQl

ex: CROSS APPLY string_split(marks,’,’)

how to perform same in mySQL

Reply
1. Michael Ryvkin says:
  
  January 25, 2021 at 10:35 am
  
  Maybe this answer would help?
  
  Reply
vivek says:

November 8, 2021 at 6:06 am

I am trying to execute

select * from table1 where table_col in( select tab2_col from table2);

here table 2 is providing ‘1,2,3,4’. I want to convert this to range so that I can get my required result.

Reply
Sunil s says:

December 7, 2021 at 7:37 am

Hi i am having the multiple product code in product master :

Product code

121, 122,123

i want to extract the value when comma separated and insert into new column

like below:

id prodcut code

1 121

2 122

3 123

Reply
Kingsley Paragon says:

May 6, 2022 at 11:04 am

Very insightful. First time using MySQL FIND_IN_SET function

I learned a lot from this post.

Reply
Rhen says:

March 12, 2024 at 7:17 am

Good article easy to implement.

Thanks a lot for this!!

Reply