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.

score 38 · Accepted Answer · answered Jun 21 '13 at 20:37

38

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

answered Jun 21 '13 at 20:37

Brian Hoover

7,861
2
28
41

Exactly, it's just a one time thing. This worked perfectly for the 8 variations. – Mazka Jun 22 '13 at 15:17

score 3 · Answer 2 · answered Jun 21 '13 at 20:31

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.

Hart CO · Answer 3 · 2013-06-21T20:33:14.253

0

Instead of DELETE, do UPDATE, something like:

UPDATE Location
SET Name = LEFT(Name,LOCATE('(',Name)-1)
WHERE LOCATE('(',Name) > 0

Syntax is off a bit I think.

edited Jun 21 '13 at 20:33

answered Jun 21 '13 at 20:28

Hart CO

34,064
6
48
63

score 0 · Answer 4 · answered Jun 21 '13 at 20:40

I made this example for you:

set @name = "Rurk Mount, Moscow, Russia(mountain)";

set @fir = locate("(",@name);
set @las = locate(")",@name);

set @lef = left(@name,@fir-1);

set @word = substr(@name,@fir+1,@las-@fir-1);

select @fir,@las,@lef,@word,concat(@lef,' ',@word);

In this way you can see a way to accomplish your task.

MySQL - Remove substring from an entry

4 Answers4