Is there a way get name of a file without its extension through SQL?

Question

I am writing a query that requires the name of a file from its directory location without its file path and file extension

Example: If a path exist c:\temp\example.xls I need the result of the query as just example .

I have referred to the site https://www.techonthenet.com/oracle/questions/filename.php , It shows a method of getting the filename with the extension. I was wondering is there another way just to get the filename.

This is the code I have used:

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;

It creates a Function to get the filename from a location with its extension.

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

This SQL statement would return example.xls.Is there a way to just get example as the result.

Answer 1

You'd remove extension; search for the last dot (or, the first one if you look from right to left) and extract everything from the 1st character to the dot position. For example:

SQL> WITH test (v_file) AS
  2    (SELECT 'my example.xls' FROM DUAL)
  3  SELECT SUBSTR (v_file, 1, INSTR (v_file, '.', -1) - 1) result
  4    FROM test;

RESULT
----------
my example

SQL>

In your case, that would be

return SUBSTR (v_file, 1, INSTR (v_file, '.', -1) - 1)