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

Question: I have a string that contains the full path to a file. How can I extract the filename (including the suffix) 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:

'example.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_filename
   (p_path IN VARCHAR2)
   RETURN varchar2

IS
   v_file VARCHAR2(100);

BEGIN

   -- Parse string for UNIX system
   IF INSTR(p_path,'/') > 0 THEN
      v_file := SUBSTR(p_path,(INSTR(p_path,'/',-1,1)+1),length(p_path));

   -- Parse string for Windows system
   ELSIF INSTR(p_path,'\') > 0 THEN
      v_file := SUBSTR(p_path,(INSTR(p_path,'\',-1,1)+1),length(p_path));

   -- If no slashes were found, return the original string
   ELSE
      v_file := p_path;

   END IF;

   RETURN v_file;

END;

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

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

This SQL statement would return 'example.xls'.