Oracle/PLSQL: Extract the filename suffix from a full file path

Question: I have a string that contains the full path to a file. How can I extract the suffix for a filename from the string in Oracle?

For example, if I have a full file path as follows:

'c:\windows\temp\example.xls'

I want to retrieve the following:

'xls'

Answer: You can create a custom function in Oracle that will retrieve the filename from the string value.

The following function called get_filename will extract the directory path. It will work with both Windows and UNIX system file paths.

CREATE or REPLACE function get_file_suffix
   (p_path IN VARCHAR2)
RETURN varchar2

IS
     v_file_suffix VARCHAR2(10);

BEGIN

    v_file_suffix := substr(p_path,(instr(p_path,'.',-1,1)+1),length(p_path));
    RETURN v_file_suffix;

EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;

END;

Once the above function has been created, you can reference this function in your SQL statement. For example,

SELECT get_file_suffix('c:\windows\temp\example.xls')
FROM dual;

This SQL statement would return 'xls'.