Search code examples
mysqlsqlstringvarchar

MYSQL: Get substring from column value?

I have the following Varchar data in a column in my MYSQL table:

Blank_Person_ID_776
Person_999

I want to extract the final number after the underscore to a variable (in this case 776) in order to use it in a query. I.e. ignore any underscore but the last one.

How can I do so?

I would like my final query to be as follows:

SET @personId= //query to get id;
Update Person set tracking_id = @personId where tracking_id is null;
Solution

  • If you want the final value after the last '_', use substring_index():

    select substring_index(<whatever>, '_', -1)

    If you specifically want the final number in the string, even when there are characters after:

    select regexp_replace(<whatever>, '.*_([0-9]+)[^0-9]*$', '$1')