Menu
I would like to extract the file extension from a field in MySQL that contains filenames. This means I need to find the final '.' character in the field and extract everything after that. The following code example partially works:
SELECT LCASE(RIGHT(filename, LENGTH(filename) - LOCATE('.', filename)))
FROM mytable;
except that it falls down for cases where the file name contains more than one '.', where it extracts too much. In most programming languages I'd expect to find a function that gives me a rightmost match, but I can't find any such thing for MySQL, nor can I find any discussion from people who have had the same problem and found a workaround.
291
Learn MySQL from scratch for Data Science and Analytics To get the first n characters of string with MySQL, use LEFT(). To get the last n char of string, the RIGHT() method is used in MySQL.
MySQL LOCATE() Function The LOCATE() function returns the position of the first occurrence of a substring in a string. If the substring is not found within the original string, this function returns 0.
To fetch the first alphabet from the strings, use LEFT(). This method allows you to return characters from the left of the string.
There is the substring_index function - it does exactly what you are looking for:
SELECT substring_index(filename, '.', -1) FROM mytable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
