Oracle/PLSQL: Extract the directory path from a full file path

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

'c:\windows\temp'

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

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

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

IS
     v_dir VARCHAR2(1500);

BEGIN

    -- Parse string for UNIX system
    IF instr(p_path,'/') > 0 THEN
        v_dir := substr(p_path,1,(instr(p_path,'/',-1,1)-1));

    -- Parse string for Windows system
    ELSIF instr(p_path,'\') > 0 THEN
        v_dir := substr(p_path,1,(instr(p_path,'\',-1,1)-1));

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

    RETURN v_dir;

END;

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

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

This SQL statement would return 'c:\windows\temp'.