How to split and search in comma-separated string 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 | 
+--------+

You May Also Like

Comments

  1. 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
    
  2. 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.

  3. 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?

    1. 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
      );
      
  4. 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?

    1. 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)?

  5. 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.

  6. 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

  7. 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');
    
    1. 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;
      
  8. 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.

  9. 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.

  10. Hi what’s about searching comma separated list in comma separated list

    SELECT FIND_IN_SET('C,A', 'A,B,C,D') AS result;

  11. how to perform split string and cross apply in mySQl

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

    how to perform same in mySQL

  12. 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.

  13. 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

  14. Very insightful. First time using MySQL FIND_IN_SET function 

    I learned a lot from this post.

Leave a Reply to atsham Cancel reply

(optional)

This site uses Akismet to reduce spam. Learn how your comment data is processed.