Search code examples
phpmysqlsql-deletefind-in-set

Delete some part of a row from database ( a value in a set of the daatabase row )

I want to delete a part of my row in mysql. like this:

my row: 1,2,3,4

I want to make it: 1,2,3

how can I do that?

I know it can be done with this code but is there a better way?

UPDATE Table SET message = REPLACE(message, ",4", "") WHERE id = 1;

Solution

  • So this will work.

    UPDATE Table SET message = CASE
WHEN message LIKE '4,%'
THEN // enter code here to replace '4,' in message with ''
WHEN message LIKE '%,4,%'
THEN // enter code here to replace ',4,' in message with ','
WHEN message LIKE '%,4'
THEN // enter code here to replace ',4' in message with ''
ELSE // this means all other occurances of 4 like 14,41,44,etc do nothing here or skip this else condition
END;