Replace specific value in comma separated list using SQL

Question

I need to replace specific values ​​in a comma separated list.

For example, suppose I have a column named Tags, populated with the following content: Holidays, Holidays 2023, Test.

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

I tried using the replace function and LIKE in the WHERE clause, but it didn't work. I'm using PHP as backend, but I don't know how to avoid this.

The following is the SQL I use:

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

Answer 1

Add a leading comma and space before your value, then add a trailing comma - then replace , Holidays, with , Holiday,.
nRemove leading and trailing commas in the result again afterwards

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

Example of using specific values ​​No Holidays, Holidays, Holidays 2023, Holidays, Test, Holidays inserted directly instead of getting it from the holidays column:

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

The result will be:

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