0

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,', '')

branman3542
  • 35
  • 5
  • 3
    Why do you store data as comma separated values? Fix your design, and you will not have this problem anymore. – jarlh Sep 05 '22 at 20:13
  • 1
    https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Barmar Sep 05 '22 at 20:14
  • The first rule of database normalisation is that every value should be atomic; you are breaking this basic premise and now seeing why it's not a good idea. – Stu Sep 05 '22 at 20:28
  • @jarlh what would be the best solution to appending data to a column then? it needs to act similarly to an array – branman3542 Sep 05 '22 at 21:00
  • Can 10 be by itself in a row, without any commas? If so, do you want this replaced with? blank, null, or possibly ignored and return 10 by itself? You should provide some data (including edge cases) and expected output. – griv Sep 05 '22 at 21:31

1 Answers1

0

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.

Yann39
  • 14,285
  • 11
  • 56
  • 84
  • Logic works, but I needed it to `update`. `UPDATE UserDB.Programs SET WorkoutID = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', WorkoutID, ','),',7,',',')) WHERE ProgramID = '7172';` – branman3542 Sep 05 '22 at 22:32
  • Yes of course, adapt the logic to your needs. But again, if you have control over the model, you better modify it for proper normalization, believe me you will avoid many problems later on :) – Yann39 Sep 06 '22 at 07:19
  • what would be a better alternative to comma separated list in a column if the list will always be 1-3 digit numbers? – branman3542 Sep 07 '22 at 23:00
  • You should have a `Workout` table holdings the IDs as records, then if you can have multiple workouts per program, and if a workout can appear in multiple program (many to many relationship), then you also need an association table, i.e. `WorkoutProgram`. I made another [fiddle](https://dbfiddle.uk/qZu__mJh) for demonstration of what it could be. – Yann39 Sep 08 '22 at 06:45