-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.

Improve this question

1 Answer 1

Reset to default
3

you have to use

and do something like this:

UPDATE mytable
SET mycolumn = SUBSTRING_INDEX(mycolumn,',', 2)
Improve this answer
5
  • 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, 2014 at 15:42
  • Also it says no rows affected.
    – Kimmax
    Jul 8, 2014 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, 2014 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, 2014 at 10:25
  • 1
    Okay I solved by removing the UNIQUE key and setting it back on afterwards. Thank you!
    – Kimmax
    Jul 9, 2014 at 10:30

Not the answer you're looking for? Browse other questions tagged .