Replace a specific value in a comma-separated list in SQL

Question

I need to replace a specific value in a comma separated list.

Let say for example that i have the column Tags populated as following: Holidays, Holidays 2023, Test.

I need to replace only the word 'Holidays' with 'Holiday' but i don't want to replace as well 'Holidays 2023' with 'Holiday 2023'.

I've tried to use replace function and LIKE in the WHERE clause but it doesn't work, i'm using PHP for back-end but i don't know how i can avoid this situation.

Here the SQL that i have used:

UPDATE Expenses SET Tags = REPLACE(Tags, :original, :replace), Updated_date = :update_date WHERE Id_user = :id_user AND Tags LIKE :original_like

:original contain the list: Holidays, Holidays 2023, Test
:replace contain only the word Holiday

@FarhaMansuri not sure what your asking, if i run this query i get the column Tags updated like this: **Holiday, Holiday 2023, Test** instead of this **Holiday, Holidays 2023, Test** — Laurenz, Aug 02 '23 at 09:27
':replace contain only the word Holiday' - needs a comma at the end..or use susbtring_index to identify the first occurence of holidays. — P.Salmon, Aug 02 '23 at 09:31
This whole question, and problem, is an [X-Y Problem](http://xyproblem.info/) which is a symptom of your bad, denormalised database design. The tags should be stored in separate rows in a secondary table with a foreign key back to the expenses table. Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) and then amend your database so you can query and save this information easily, row by row. — ADyson, Aug 02 '23 at 09:37
Although I agree a database schema change would be better in the long run, because you tagged this with PHP, I’d tackle this problem with it. SELECT the record, get the field, parse CSV to array, do replacement, join array with commas, UPDATE the record — Chris Haas, Aug 02 '23 at 11:17
@ADyson thanks for the suggestions, i'm working to normalize the database schema. I'm creating a new table that collect Expense ID and Tag ID (one-to-many) so i will be able to store multiple tags row by row. — Laurenz, Aug 02 '23 at 15:37

CBroe · Answer 1 · 2023-08-02T11:24:25.923

Add a leading comma and space to your value first, and a trailing comma - and then replace
, Holidays, in that with , Holiday,.
Remove the leading and trailing commas from the result again afterwards.

SELECT TRIM(',' FROM REPLACE(CONCAT(', ', Tags, ','), ', Holidays,', ', Holiday,'))

Example with specific value No Holidays, Holidays, Holidays 2023, Holidays, Test, Holidays inserted directly instead of getting it from the Tags column:

SELECT TRIM(',' FROM REPLACE(
  CONCAT(', ', 'No Holidays, Holidays, Holidays 2023, Holidays, Test, Holidays', ','),
  ', Holidays,',
  ', Holiday,')
)

will result in:

 No Holidays, Holiday, Holidays 2023, Holiday, Test, Holiday

Thanks for the answer, but i think that the best way to resolve the issue is to review the schema of the DB, as they suggested to me — Laurenz, Aug 02 '23 at 15:38

Replace a specific value in a comma-separated list in SQL

1 Answers1