Search code examples
mysqlsubstringrights

Extract the first number right before a string in mysql query

i have an image filename like this:

5110b99ba85c72_t.jpg

i want to extract the first number right before the string _t or _m or something that starts with _. In this case, it is the number 2.

I've found 2 solutions that are working ok, but not sure wich is the better one, the pros or cons of both methods...

What do the experts think?

Method 1

SELECT SUBSTRING(SUBSTRING_INDEX(thumb_filename, '_', 1), -1) FROM image

Method 2

SELECT RIGHT(SUBSTRING_INDEX(thumb_filename, '_', 1), 1) FROM image
Solution

  • I'm fairly sure there wont be much of a difference in terms of performance between the two.

    That said, I would probably use the RIGHT() function because it is a little clearer what exactly you are trying to do.

    If you were worried about performance though, I would add a column with that value and keep it up to date when the file changes. Much faster to get the number once when the row is generated/updated and then not have to be doing SUBSTRING functions in the query.