MySQL regular expression to select the last integer in the string

Question

I have a MySQL database table that contains column run that is an integer and another column filename that is a varchar with typical values like RUN0001.FTS or 3DS0231.FTS or 3RUN0010.FTS

I need to check the values in the run and filename columns. The prescription is I extract the last integer (without leading zeroes) before the dot character (.) in the filename column, and compare it to the value of the run column. How do I write a select statement to do this comparison to return the rows that will not have the matching integers?

For example, a regular expression I am trying to build would extract 1 from RUN0001.FTS, or 231 from 3DS0231.FTS, or 10 from 3RUN0010.FTS and then compare it to the value in the run column, and return the primary key if the two don't match.

In Python I would manipulate the variable filename = '3RUN0010.FTS' like so:

import re
filename = '3RUN0010.FTS'
fileRunNumber = re.findall('\d+', filename)
runNumber = int(fileRunNumber[-1])
print "The integer I want is", runNumber

How do I do this in as a MySQL statement?

Thanks, Aina.

In MySQL the only thing you can do with a regular expression is test whether something matches it. You can't use it to extract parts of a string. There are third-party UDFs that can do this, search for them with Google. — Barmar
– Barmar, Commented Mar 31, 2017 at 4:06

Tim Biegeleisen · Accepted Answer · 2017-03-31 04:14:34Z

2

You can try the following query:

SELECT CAST(SUBSTRING(col, INSTR(col, '.') - 4, 4) AS UNSIGNED) AS theNumber
FROM yourTable

Data:

CREATE TABLE yourTable (col varchar(55));
INSERT INTO yourTable (col)
VALUES
    ('RUN0001.FTS'),
    ('3DS0231.FTS'),
    ('3RUN0010.FTS');

Output:

Demo here:

Rextester

answered Mar 31, 2017 at 4:14

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

Aina Over a year ago

Great, this has totally solved my problem and is exactly what I needed, thanks, Tim!

Collectives™ on Stack Overflow

MySQL regular expression to select the last integer in the string

1 Answer 1

Rextester

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related