CODEWITHSUNDEEP
mysqlsqlphpmyadmin
ricardo
ricardo

Reputation: 215

Mysql Query to remove unwanted letters from the rows

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

Upvotes: 0

Views: 220

Answers (1)

SubRed
SubRed

Reputation: 3187

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.

Upvotes: 2

Related Questions