MySQL: REGEXP to remove part of a record

I have a table "locales" with a column named "name". The records in name always begin with a number of characters folowed by an underscore (ie "foo_", "bar开发者_运维知识库_"...). The record can have more then one underscore and the pattern before the underscore may be repeated (ie "foo_bar_", "foo_foo_").

How, with a simple query, can I get rid of everything before the first underscore including the first underscore itself? I know how to do this in PHP, but I cannot understand how to do it in MySQL.

SELECT LOCATE('_', 'foo_bar_') ... will give you the location of the first underscore and SUBSTR('foo_bar_', LOCATE('_', 'foo_bar_')) will give you the substring starting from the first underscore. If you want to get rid of that one, too, increment the locate-value by one.

If you now want to replace the values in the tables itself, you can do this with an update-statement like UPDATE table SET column = SUBSTR(column, LOCATE('_', column)).

select substring('foo_bar_text' from locate('_','foo_bar_text'))

MySQL REGEXs can only match data, they can't do replacements. You'd need to do the replacing client-side in your PHP script, or use standard string operations in MySQL to do the changes.

UPDATE sometable SET somefield=RIGHT(LENGTH(somefield) - LOCATE('_', somefield));

Probably got some off-by-one errors in there, but that's the basic way of going about it.