Working with paths in SQL. Is there an easy way?

Question

MySQL

Example - sqlfiddle.com/#!9/a2612
There is a column with paths to files, for example '/a/b/c/110/h4eheh.jpg' You
need to get only the last folder, extension from it and insert a static string between them . Those. the new name should be '110_somestring.jpg'
Is it possible to write a query without a bunch of nested functions?
PS Please do not offer to implement this in python/php/node.js, etc.

Answer 1

SELECT CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX('/a/b/c/110/h4eheh.jpg','/',-2),'/',1),'_somestring.',SUBSTRING_INDEX('/a/b/c/110/h4eheh.jpg','.',-1)) as image

to understand where it came from, here is the "source"

SET @q = '/a/b/c/110/h4eheh.jpg';
SET @a1 = SUBSTRING_INDEX(SUBSTRING_INDEX(@q,'/',-2),'/',1);
SET @a2 = SUBSTRING_INDEX(@q,'.',-1);
SELECT @q, CONCAT(@a1,'_somestring.',@a2),@a2

Answer 2

explode by /, then take only the last and third from the end, then glue back together via GROUP_CONCAT.
in principle, nothing prevents the function from being corrected so that it can be glued back together.