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
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!