MySQL: Split comma separated list into multiple rows

by

In MySQL this can be achieved as below 

SELECT id, length FROM vehicles WHERE id IN ( 117, 148, 126) 

+---------------+
| id  | length  |
+---------------+
| 117 | 25      |
| 126 | 8       |
| 148 | 10      |
+---------------+

SELECT id,vehicle_ids FROM load_plan_configs WHERE load_plan_configs.id =42

+---------------------+
| id  | vehicle_ids   |
+---------------------+
| 42  | 117, 148, 126 |
+---------------------+

Now to get the length of comma separated vehicle_ids use below query

Output

SELECT length 
FROM   vehicles, load_plan_configs   
WHERE  load_plan_configs.id = 42 AND FIND_IN_SET(
       vehicles.id, load_plan_configs.vehicle_ids
)

+---------+
| length  |
+---------+
| 25      |
| 8       |
| 10      |
+---------+

For more info visit http://amitbrothers.blogspot.in/2014/03/mysql-split-comma-separated-list-into.html

More Related Contents:

  1. SQL split values to multiple rows
  2. SQL Query needed for fetch value from Two identical table but not fully identical table
  3. SQL select only rows with max value on a column [duplicate]
  4. How to create a MySQL hierarchical recursive query?
  5. How to reset AUTO_INCREMENT in MySQL
  6. How to declare a variable in MySQL?
  7. MySQL Query GROUP BY day / month / year
  8. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  9. SQL counting all rows instead of counting individual rows
  10. How do I use on delete cascade in mysql?
  11. Adding a leading zero to some values in column in MySQL
  12. Using SQL to determine word count stats of a text field
  13. Update one MySQL table with values from another
  14. MySQL JOIN the most recent row only?
  15. How to transpose mysql table rows into columns
  16. MySQL Group By and Sum total value of other column
  17. MySQL incorrect key file for tmp table when making multiple joins
  18. Search all columns of a table using a single where condition with single keyword in mysql
  19. What does SQL clause “GROUP BY 1” mean?
  20. Does the order of conditions in a WHERE clause affect MySQL performance?
  21. Syntax error when using except in a query
  22. MySQL query, MAX() + GROUP BY
  23. MySQL export into outfile : CSV escaping chars
  24. Why the order of evaluation for expressions involving user variables is undefined?
  25. ALTER table – adding AUTOINCREMENT in MySQL
  26. sql like operator on integer
  27. MySQL – SELECT * INTO OUTFILE LOCAL ?
  28. The best way to remove value from SET field?
  29. How do you force mysql LIKE to be case sensitive? [duplicate]
  30. In what order are MySQL JOINs evaluated?

Leave a Comment