How to remove comma separated string of records in MySQL?

Question

How to remove comma separated string "0000-00-00"

  ID      Name   Return Date
   1       A     0000-00-00,2016-02-1,2016-1-15
   2       B     0000-00-00,2016-04-1
   3       c     0000-00-00,2016-04-4

Expecting Answer

  ID      Name   Return Date
   1       A     2016-02-1,2016-1-15
   2       B     2016-04-1
   3       c     2016-04-4

Answer 1

think you have 3 cases: 0000-00-00string at left, right and middle:

+------+------+--------------------------------+
| ID   | Name | Return Date                    |
+------+------+--------------------------------+
|    1 | A    | 0000-00-00,2016-02-1,2016-1-15 |
|    2 | B    | 0000-00-00,2016-04-1           |
|    3 | C    | 0000-00-00,2016-04-4           |
+------+------+--------------------------------+

Using the REPLACE function:

SELECT `Return Date`, REPLACE(`Return Date`,'0000-00-00,','') as replaced
FROM YourTable;

+--------------------------------+----------------------+
| Return Date                    | replaced             |
+--------------------------------+----------------------+
| 0000-00-00,2016-02-1,2016-1-15 | 2016-02-1,2016-1-15  |
| 0000-00-00,2016-04-1           | 2016-04-1            |
| 0000-00-00,2016-04-4           | 2016-04-4            |
+--------------------------------+----------------------+

Your update sentence would be:

UPDATE YourTable
SET `Return Date` = REPLACE(`Return Date`,'0000-00-00,','') 
WHERE `Return Date` like '%0000-00-00,%';

You must do similar queries for the other cases like '0000-00-00' at the middle or right.