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

| 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 '%(%)%'

CodePudding user response：

You can use regexp_replace:

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

CodePudding user response：

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 '%(%)%'

CodePudding user response：

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.