MySQL function to return extension

Question

I have the following in a sql query:

SELECT field1, field2, ... substring_index(file_name, '.', -1)
FROM table

I use this function quite frequently to grab the extension so I'd just like to extract that to a function. How would I do that in mysql? So far I have something like:

CREATE PROCEDURE get_extension(field VARCHAR)
                 returns VARCHAR 
                 SUBSTRING_INDEX(field, '.', -1)

You mean a funcion, not a procedure - these are 2 different things. — juergen d, Jun 02 '21 at 04:33
@juergend oh, I wasn't familiar: what's the difference between the two in mysql? — David542, Jun 02 '21 at 04:40
https://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when — juergen d, Jun 02 '21 at 05:12
*I use this function quite frequently to grab the extension so I'd just like to extract that to a function.* 1) This will decrease the performance. 2) Your function must take into account that the filename may have no extension. And that the last dot may be both delimiter between name and empty extension and a char of a name in this case (in last case the file is not accessible without UNC filename usage) - and these choices are not distinguishable. — Akina, Jun 02 '21 at 05:35
@Akina why would it decrease performance out of curiosity? Want to post and answer with all your comments showing how it'd be done instead and I'll accept that? — David542, Jun 02 '21 at 15:21
*why would it decrease performance* Function calling needs in additional time and resources, is it? And AFAIR functions are executed in interpretational mode. — Akina, Jun 02 '21 at 15:35
@Akina is it just the time of one additional function call? (negligible) or is it one additional function call per-row-or-data ? — David542, Jun 02 '21 at 15:45
@Akina hmm...what's the difference then to call an internal function like `concat` and a user-defined function then? Would the optimizer just compile a deterministic function once and use it just like any other function? — David542, Jun 02 '21 at 18:00
*Would the optimizer just compile a deterministic function once and use it just like any other function?* No. There is no compilation - or it is so hidden that it is not mentioned in RM. — Akina, Jun 02 '21 at 18:03
o_O Reference Manual. https://dev.mysql.com/doc/refman/8.0/en/ — Akina, Jun 02 '21 at 18:36

score 1 · Accepted Answer · answered Jun 02 '21 at 04:44

1

You're close, here is an example:

CREATE FUNCTION get_extension (s VARCHAR(200)) 
       RETURNS VARCHAR(10) DETERMINISTIC
       RETURN substring_index(s, '.', -1);

And now you can do:

SELECT get_extension(file_name), ... FROM table;

answered Jun 02 '21 at 04:44

David542

104,438
178
489
842

I fail to see what advantage this offers over the original – Strawberry Jun 02 '21 at 05:00
But keep in mind that SQL is NOT OOP. Don't try to apply OOP programming methods on SQL. – juergen d Jun 02 '21 at 05:14
@Strawberry for me it's I often forget the syntax of `SUBSTRING_INDEX` and have to look it up ever couple days when I forget the exact syntax. So it gets rid of the three arguments for me and makes it clear how to use this. – David542 Jun 02 '21 at 15:22
@juergend could you please explain your comment a bit? What do you mean by OOP -- the above is just a function to make another function less confusing / easier to remember for me – David542 Jun 02 '21 at 15:46
1

@David542: SQL is all about performance. Don't try to make things look nice. You might kill an index usage or whatever if you unnecessarily hide functionality in functions or do things you would do in a normal programming language. – juergen d Jun 03 '21 at 03:35

MySQL function to return extension

1 Answers1