Query to split the comma seperated column value into the multiple rows

Question

i have one mysql table name called mem_exam_dates.

+---------+-------------+----------------------------------+
| rec_id  |  mem_name   |            exam_dates            |
+---------+-------------+----------------------------------+
|    1    |    Raju     | 2015-01-05,2015-05-09,2018-05-09 |
|    2    |    Rajes    | 2015-10-05,2015-12-09,2018-09-09 |
+---------+-------------+----------------------------------+

now i want to display the result as below.

+-------+---------------+
|  Raju |   Exam Dates  |
+-------+---------------+
|      2015-01-05       |
|      2015-05-09       |
|      2018-05-09       |
+-----------------------+

i am writing the query like

select * from mem_exam_dates where rec_id=1

from the above query i am getting total exam dates as single string.

but i want the exam dates as below.

+----------------+
|   2015-01-05   |
|   2015-05-09   |
|   2018-05-09   |
+----------------+

what is the query for that one?

if anybody knows let me know...

Thanks in advance

kalyan

If you want mysql solution i think you have to create function to do this. — Zakaria Acharki, Sep 09 '15 at 17:55
Are you storing multiple dates into one field? You should probably normalize that. — vinz, Sep 09 '15 at 17:56
Restructure your DB properly (ie, one value per line) or use a DB that supports arrays (ie, postgres). — bobflux, Sep 09 '15 at 20:01
possible duplicate of [Can Mysql Split a column?](http://stackoverflow.com/questions/1096679/can-mysql-split-a-column) — Strawberry, Sep 09 '15 at 20:41

Frank · Answer 1 · 2015-09-09T20:54:10.787

1

In MySQL you can extract a part from string with SUBSTRING_INDEX.

So you can try the follow SQL

SELECT SUBSTRING_INDEX(exam_dates, ',', 1) as first
     , SUBSTRING_INDEX(SUBSTRING_INDEX(exam_dates, ',', 1), ',', -1) as second
     , SUBSTRING_INDEX(exam_dates, ',', -1) as third

In .NET you can use split()

https://msdn.microsoft.com/de-de/library/b873y76a%28v=VS.110%29.aspx

If you have the column in a variable named exam_dates you can use:

string [] dates = exam_dates.Split(new Char [] {','});

So you have an array of all dates.

edited Sep 09 '15 at 20:54

answered Sep 09 '15 at 18:10

Frank

1,901
20
27

And if he has 10 dates ? – Zakaria Acharki Sep 09 '15 at 18:16

June Lewis · Answer 2 · 2015-09-09T17:50:53.240

0

You need to process the data in the server side language. If you are using PHP, you would use explode.

For example:

$dates= explode(",", $longdatestring);
echo $dates[0]; // date1 
echo $dates[1]; // date2

To find the number of dates returned, simply count the elements in your array.

$numberofdates = count($dates);

Or look through the array untill you run out of elements.

foreach ($dates as $date) {
    echo $date;
}

For further details in PHP: https://secure.php.net/manual/en/function.explode.php

edited Sep 09 '15 at 17:50

answered Sep 09 '15 at 17:41

June Lewis

355
1
6
28

i want only from mysql query...if i use explode...then how do i know how many dates are exists. – user71723 Sep 09 '15 at 17:45
thanks for your help..but i want to get that one using mysql query – user71723 Sep 09 '15 at 17:52

Query to split the comma seperated column value into the multiple rows

2 Answers2