0

I have a table with a column that has a string that is listed in a variety of comma-delimited ways. I need to remove the string or replace the string - is it possible to use REPLACE on the column twice or more in the same mysql query?

myTable
id    myString
================
1     15
2     15,30
3     30,45
4     30
5     30,15,45
6     45,15

I need to replace or remove all instances of 15, but I need to account for 15 being the only value OR one of several values that are separated by a comma. IF one of several values, then I need to account removing extra commas that would be left by the first replace.

Just replacing the 15 would leaving trailing commas behind:

IE: $query = "UPDATE myTable SET myString=(REPLACE(myString,'15',''))" ;

myTable
id    myString
================
1     
2     ,30
3     30,45
4     30
5     30,,45
6     45,

The replace needs to account for the comma before (or after) the value - if it exists and as well as replace any leading commas.

$query = "UPDATE myTable SET myString=(REPLACE(myString,'15','')),  // replace string
                 myString=(REPLACE(myString, ',,' , ',')),   //then remove double commas
                 myString=(REPLACE(myString, ',%' , '')),    //then leading commas
                 myString=(REPLACE(myString, '%,' , ''))" ;    //then trailing commas

Would the above work...replacing the matching with the first replace and the 2nd, 3rd, 4th replace clean up trailing commas? Or will it not work because each replace is only evaluating the original content, and not the content after it was already updated?

What is a better method to do this?

rolinger
  • 2,787
  • 1
  • 31
  • 53
  • 2
    The better method is to store values one per row, then you can use `DELETE FROM myTable WHERE value = 15;` You will find [many other problems](https://stackoverflow.com/a/3653574/20860) result from storing a string of comma-separated values and trying to work with them as if they are discrete values. – Bill Karwin Jan 30 '23 at 16:06
  • @BillKarwin - without a doubt you are correct. But poor design from the the original developer and I am taking over. So I need a method that can fix the current design without redesigning the tables. – rolinger Jan 30 '23 at 16:08
  • 1
    Redesign the tables. You're only painting over mold stains. – Bill Karwin Jan 30 '23 at 16:08
  • besides not storing delitered data, you would cascade replace statements `REPLACE(REPLACE(REPLACE('a',''),'b',''),'c','')` – nbk Jan 30 '23 at 16:08
  • 2
    I, too, would strive to redesign the tables. Anyway, a simple solution here is `update mytable set mystring = trim(both ',' from replace(concat(',', mystring, ','), ',15,', ',')) where find_in_set('15', mystring);` – Thorsten Kettner Jan 30 '23 at 16:14

1 Answers1

0

Credit to @Thorsten Kettner for providing a good solution to the issue.

update mytable set mystring = trim(both ',' from replace(concat(',', mystring, ','), ',15,', ',')) where find_in_set('15', mystring);
rolinger
  • 2,787
  • 1
  • 31
  • 53