-1

I have a mysql table storing some data. One column stores strings containing a comma. I want to get rid of everything (including the comma) in this string and save it, but I don't get it to work. I tried some solutions from here but I don't understand how substring works in this case.
Example table data:

|------
|id|Name
|------
|6695|Aalen (Württemberg)
|7001|Achern (Baden)
|7133|Aach (Hegau)
|8975|Abenberg, Mittelfranken
|17223|Abbensen, Kreis Peine
|17323|Abbenrode, Kreis Braunschweig
|17608|Abbensen, Han

I want to get it like this:

|------
|id|Name
|------
|6695|Aalen (Württemberg)
|7001|Achern (Baden)
|7133|Aach (Hegau)
|8975|Abenberg
|17223|Abbensen
|17323|Abbenrode
|17608|Abbensen

There are about 60.000 rows, so faster solutions would be good.

closed as off-topic by Jens Erat, Doktoro Reichard, and31415, Moses, Kevin Panko Jul 11 '14 at 3:39

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question is not about computer hardware or software, within the scope defined in the help center." – and31415, Kevin Panko
If this question can be reworded to fit the rules in the help center, please edit the question.

3

you have to use

and do something like this:

UPDATE mytable
SET mycolumn = SUBSTRING_INDEX(mycolumn,',', 2)
  • I used UPDATE city SET ´Name´= SUBSTRING_INDEX(´Name´,',', 2) (Used ` in the query but the formating breaks if i do it here) on the table and it executes with no error, but nothing changes. – Kimmax Jul 8 '14 at 15:42
  • Also it says no rows affected. – Kimmax Jul 8 '14 at 15:45
  • for parameter "count" you have to use "1" instead of "2" of my example. apologies for the confusion. note: "2" would refer to the 2nd delimiter (which you do not have according to your example). – udo Jul 9 '14 at 10:14
  • Okay this seems to be working. Howerver the problem now is, that duplicate entries would be created and the SQL won't execute because of this. How can I avoid that? I just wan't to keep one entry and delete the other one. – Kimmax Jul 9 '14 at 10:25
  • 1
    Okay I solved by removing the UNIQUE key and setting it back on afterwards. Thank you! – Kimmax Jul 9 '14 at 10:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.