Home Ask Login Register

Developers Planet

Your answer is one click away!

Alexandr February 2016

mysql query to search multiple values in comma separated list

I have a table Orders:

Order VARCHAR(20)

Developer VARCHAR(50)

I need to SELECT field in the following way:

For example I have this data in my Table:

Order              Developer

Order 141          Tim (Apple), Alex (Microsoft), Sara (Amazon), Neylo
Order 171          James (Apple), John (Amazon)
Order 181          Nike (Microsoft)

Need to make SQL query to get this:

Developer                Order

Tim, James (Apple)       Order 141, Order 171
Alex, Nike (Microsoft)   Order 141, Order 181
Sara, John (Amazon)      Order 141, Order 171
Neylo                    Order 141

Is this possible to make? Any idea is welcome


Frankbeen February 2016

if this is your database then it is a mess. A golden rule is to have all data only one time in your database. Therefor you should create a table "user" that have all users in his:


  • user_id
  • name
  • email
  • ...

Then you could make your order table like this:


  • order_id
  • user_id
  • ...

Now you can get all information like this:

SELECT order.order_id, user.name FROM orders
JOIN users ON order.user_id=user.user_id
WHERE order.order_id=141

Understanding JOINS in MySQL

Brian C February 2016

The best way would be to use a new table to make it atomic like others mentioned, but it sounds like you can't do that.

If you can add a stored function this solution will work. http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

If you cannot add a function (e.g. hosted with no admin acces, etc). You could break the data apart using substrings (ugly ugly ugly -- I even hate to mention it it's so bad -- but it could work if you write for the maximum number of supported values). http://stackoverflow.com/a/11008567/455627


 substring_index(`Developer`,',',1) ==> first value
 substring_index(substring_index(`Developer`,',',-2),',',1) ==> second value


Post Status

Asked in February 2016
Viewed 2,318 times
Voted 4
Answered 2 times


Leave an answer

Quote of the day: live life