Split comma separated rows in SQL
Question:
How to split a column containing comma separated values into separate rows in SQL (MySQL)?
Answer:
MySQL does not provide a built-in function for splitting comma-separated row values. However, you can use an unconventional method to achieve this, which involves counting numbers and MySQL's SUBSTRING_INDEX() function:
1 2 3 4 5 6 7 8 9 |
|
This query uses the SUBSTRING_INDEX() function to extract individual values by specifying a comma as the delimiter and an n value as the split position. Count numbers are generated using a subquery that creates a range of numbers from 1 to 100, allowing you to split up to 100 separated values per row.
For the persistent count table method, use the following query:
1 2 3 4 |
|
This method has better scalability if you need to split a large number of rows containing comma separated values.
The above is the detailed content of How to Split Comma-Separated Values in MySQL into Individual Rows?. For more information, please follow other related articles on the PHP Chinese website!