Select filename/basename from stmf path

I often come across database tables that contain full paths to stream files on the IFS, and sometimes I wish to extract just the filename/basename from the paths.

Lets say we have a table called uploaded_documents, which contain a column called stmf with the absolute path to an IFS file.

I came up with this simple snippet to accomplish this directly in SQL:

SELECT
    SUBSTRING(stmf, LOCATE_IN_STRING(stmf, '/', -1) + 1, LENGTH(stmf))
FROM uploaded_documents

If the row contains a path like this: /home/qpgmr/documents/invoice123.xml, the select will give us invoice123.xml.

This can be abstracted away with an SQL function, for improved reusability!

Creating an SQL function to extract the basename

CREATE OR REPLACE FUNCTION basename(stmf VARCHAR(1024))
RETURNS VARCHAR(1024)                                                                                   
BEGIN
  RETURN SUBSTRING(stmf, LOCATE_IN_STRING(stmf,'/',-1) + 1, LENGTH(stmf));
END

Now, simply use the function like this:

SELECT
    basename(stmf)
FROM uploaded_documents

That’s it for today!