0

I have a mysql table in which one column named 'city' is like below :

 id      city

 1       New Jersey(Trenton)

 2       New Jersey(Burlington)

 3       New Jersey(Cherry Hill)

 4       New York(Manhattan)

 5       Philadalphia(BalaCynwd)

So what query can i put in the phpmyadmin so that i can remove the name in the bracket? So i am looking for a result in which i get only city and not bracket. So it wil be New Jersey, New York and Philadelphia.. I have 3 million rows so i cannot do it manually.

So can i put like below?:

if 'city' = '%New Jersey%' then set 'city' = 'New Jersey'; ? but this even if it works ...i will have to execute lots of queries for all cities..

Thanks

ricardo
  • 215
  • 1
  • 7
  • 18

1 Answers1

2

Try this:

UPDATE table SET city=SUBSTRING_INDEX(city, '(', 1)

See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index for details.

SubRed
  • 3,169
  • 1
  • 18
  • 17
  • 1
    +1 Although this won't work, when there are additional words after the braces. – Olaf Dietsche Dec 26 '12 at 02:09
  • @OlafDietsche you're right since this only takes the first substring before delimiter. This only works when his column values just like what in his question above. – SubRed Dec 26 '12 at 02:12
  • I am just making a backup and will try @OlafDietsche query soon...thanks – ricardo Dec 26 '12 at 02:20
  • I tried this UPDATE `jos_jbjobs_jobseeker` SET 'city' = SUBSTRING_INDEX(city, '(', 1); and I get an error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''city' = SUBSTRING_INDEX(city, '(', 1)' at line 1 – ricardo Dec 26 '12 at 02:23
  • @ricardo just remove quotes from `'city'`. `UPDATE jos_jbjobs_jobseeker SET city = SUBSTRING_INDEX(city, '(', 1)` – SubRed Dec 26 '12 at 02:24
  • yep thank@SubRed DONE !! I put SELECT DISTINCT city from the table and i got the result which i wanted..many thanks to you and Merry Christmas. – ricardo Dec 26 '12 at 02:33