CODEWITHSUNDEEP
mysql
user71723
user71723

Reputation: 343

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

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

Upvotes: 0

Views: 648

Answers (2)

Frank
Frank

Reputation: 2043

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.

Upvotes: 1

June Lewis
June Lewis

Reputation: 355

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

Upvotes: 0

Related Questions