How do you remove certain characters from a string in MySQL?

user_17303569 0
Viewed 359
| city             |
| ---------------- |
| Chicago (IL IN)  |
| New (NY) York    |
| (ARZ) Phoenix    |

Is there a way to remove (IL IN), (NY), and (ARZ)?

I've tried the code below but it doesn't work.

UPDATE table
SET city = REPLACE(city, '(%)', '')
WHERE city LIKE '%(%)%'
mysql sql
3 Answers

You can use regexp_replace:

update table set city = regexp_replace(city, '\\(\\w{2,}(\\s\\w{2,})*\\)', '')
user_73267380

This might work, it also removes extra space if any

update table1
set city = regexp_replace(city, '\\([\\w\\s]+\\)\\s*|\\s*\\([\\w\\s]+\\)', '')
where city like '%(%)%'
user_170161440

Try running this and see what is the result:

SELECT city, 
       CONCAT(SUBSTRING(city,1,LOCATE('(',city)-1),SUBSTRING(city,LOCATE(')',city)+2)) AS trimmed_city
FROM mytable
WHERE city LIKE '%(%)%';

If the result of trimmed_city is what you want, then you can update it like:

UPDATE table
SET city=CONCAT(SUBSTRING(city,1,LOCATE('(',city)-1),SUBSTRING(city,LOCATE(')',city)+2)
WHERE city LIKE '%(%)%';

This probably for older MySQL version that doesn't support REGEXP_REPLACE() like @Ajax1234's answer.

user_109106920
Related