MYSQL: Get substring from column value?

Question

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;

Gordon Linoff · Accepted Answer · 2021-07-14 15:31:32Z

2

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

answered Jul 14, 2021 at 15:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

java12399900 Over a year ago

Thanks for this Gordon, the issue with this is that there will be string with e.g. 3 underscores and some with 1. I have updated my questionto reflect this.

Gordon Linoff Over a year ago

@java12399900 . . . That is not an issue for either of these approaches. Both should work.

java12399900 Over a year ago

OK but what do I replace the "whatever" with to cover both cases?

Collectives™ on Stack Overflow

MYSQL: Get substring from column value?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related