MYSQL replace depending on comma position-CodePudding

If I have a value in column WorkoutID that looks like 100,10,7

and I want to remove the value 10 from this, I can use the following SQL script:

UPDATE 
    UserDB.Programs 
SET
    WorkoutID = REPLACE(WorkoutID, ',10','')
WHERE
    ProgramID = '7172';

which would correctly output 100,7.

The expected outcome ALWAYS needs to be number, number, or number

NOT number,,number, or number, or ,number

which makes it tricky because in the replace statement, i need to look for the value, but how can I assume the comma position? i.e replace(WorkoutID, ',10', ''), or replace(WorkoutID, '10,', '')

CodePudding user response：

As others pointed out in the comment, you should not store comma separated values in a single column.

But in case you have no control over the data, here is a solution.

The problem with your solution is that you may unintentionally remove parts of other values, i.e. if you have something like 100,10,7,1000 and remove ,10, you will get 100,700...

One solution would be to add a leading and trailing comma to the original string, then replace the value enclosed with commas (i.e. ,10,), then remove the added leading and trailing commas.

Example :

CREATE TABLE program (ProgramID  INT, WorkoutID TEXT);
INSERT INTO program VALUES (1, '100,12,4,55,120,212,45');

SELECT TRIM(BOTH ',' FROM REPLACE(CONCAT(',', WorkoutID, ','),',12,',','))
FROM program;

Result :

100,4,55,120,212,45

Fiddle

There may be other solutions using JSON paths etc. but I think this one is pretty fast and understandable.