how mysql search for remove date necessary when feild comma delimited

Question

i have data in mysql feild and i want to search and remove year and month like '%2019-11%' by select command but it not in group from data like this :

2019-10-01,2019-10-03,2019-10-07,2019-10-09,2019-11-15,2019-11-17

or

2019-10-01,2019-11-17,2019-10-07

i want resutl like this :

2019-10-01,2019-10-03,2019-10-07,2019-10-09

or

2019-10-01,2019-10-07

Please, show me the command

'Please, show me the command' - there isn't one. sql does not expect data to be stored as csv strings(arrays?) and never expected users to do so. You are going to have to write code to split and reconstitute the string. Please search for mysql split string as a start point. — P.Salmon, Sep 30 '19 at 08:38

GMB · Answer 1 · 2019-09-30T08:47:13.270

If you are running MySQL 8.0, you can use REGEXP_REPLACE() twice, like:

REGEXP_REPLACE(REGEXP_REPLACE(x, '2019-11-[0-9]{2},*', ''), ',$', '')

Demo on DB Fiddle:

    WITH t AS (
      SELECT '2019-10-01,2019-10-03,2019-10-07,2019-10-09,2019-11-15,2019-11-17' x
      UNION ALL SELECT '2019-10-01,2019-11-17,2019-10-07'
    )
    SELECT x, REGEXP_REPLACE(REGEXP_REPLACE(x, '2019-11-[0-9]{2},*', ''), ',$', '') x2 FROM t;

| x                                                                 | x2                                          |
| ----------------------------------------------------------------- | ------------------------------------------- |
| 2019-10-01,2019-10-03,2019-10-07,2019-10-09,2019-11-15,2019-11-17 | 2019-10-01,2019-10-03,2019-10-07,2019-10-09 |
| 2019-10-01,2019-11-17,2019-10-07                                  | 2019-10-01,2019-10-07                       |

Here is another option with a unique call to REGEXP_REPLAE():

REGEXP_REPLACE(x, '(2019-11-[0-9]{2},*)|(,*2019-11-[0-9]{2})', '')

Demo on DB Fiddle

how mysql search for remove date necessary when feild comma delimited

1 Answers1