to add to an existing mysql table I use concat so I add data in table

1,5,45,265

What do I do if I want to remove one or two items, say 45 so it becomes

1,5,265

thanks

    You have to rethink your database design. Read up on database normalization to understand why and how to do it. Phpbuilder have one article about it, google returns lots of hits.

      As the MySQL manual suggests when using the SET Datatype:

      To remove set elements from an existing set, we use the REPLACE function to remove the element. If using decimal values, we use a combination of the bitwise AND operator & with the bitwise NOT operator ~.

      UPDATE set_test SET myset = REPLACE(myset,'Dancing','')
      WHERE rowid = 6;

      EDIT: Well that's a bit misleading... just tried the code above myself, and MySQL gave me an error. Since there doesn't seem to be an easy way to remove an item from a set without using regexp, you might do as suggested and rethink the structure.

        yes, comma seperated values in databases are usually big red flags because of the varieties of ways it can be entered it makes the data unusable. and in the end it doesnt save any space or speed.

          Write a Reply...