MySQL - Remove substring from an entry

Question

My 'Location' table contains 6 columns. (ID, Name, Alias, Area, X, Y)

Example of some entries from the 'Name' column:

Blackwood Highschool, Paris, France

Hilltop Market, Barcelona, Spain

Roundwell Plaza, Melbourne, Australia

Rurk Mount, Moscow, Russia(mountain)

History Museum, Prague, Czech Republic

Narrow River (river), Bombay, India

Some entries include "(mountain)", "(river)" or "(...)" within the name (8 different ones). I don't know why the table was created this way. It should have had an extra column for this data, but well.

I want to remove just the "(...)" substrings from the Location Names. I don't know how to do it, it's something like this so you get an idea:

DELETE FROM 'Location' 
WHERE 'Name'
LIKE '%(%)%';

I know this would delete the whole row, but I just want to remove the (%) term from the 'Name' string.

Answer 1

If you only have 8 variations, and this is a one time thing, you could do it with a replace.

update location
  set name = replace(name , '(river)','')
  where name like '%(river)%';

Answer 2

You can do this with brute force string operations:

select concat(left(name, instr(name, '(') - 1),
              right(name, length(name) - instr(val, ')'))
             )

Actually, you want this in an update statement:

update location
    set name = concat(left(name, instr(name, '(') - 1),
                      right(name, length(name) - instr(val, ')'))
                     )
    where name like '%(%)%';

You do not want delete, because that deletes entire rows.