How to extract the filename from a given file path using different SQL databases: Oracle, MySQL, SQL Server, and PostgreSQL.

How to extract the filename from a given file path using different SQL databases: Oracle, MySQL, SQL Server, and PostgreSQL.

 

Youtube Video : Video

---------------  ORACLE

with cte as (

select 'c:/home/user/sreenu/test/emp.txt' as filename from dual union all 

select 'd:home/dept.txt' as filename from dual union all 

select 'e:/salgrade.csv' as filename from dual union all 

select 'c:/home/user/test1.txt' as filename from dual 

)

select 

substr(filename,instr(filename,'/',-1,1)+1) file1,

reverse(substr(reverse(filename),1,instr(reverse(filename),'/',1,1)-1)) file2,

REGEXP_SUBSTR(filename, '[^/]+$') file3,

REGEXP_REPLACE(filename, '.*[\\/]', '') AS file4

from cte;

REGEXP_SUBSTR(file_path, '[^/]+$') is used to extract the part of the string that matches the given regular expression.

[^/]+$ matches one or more characters ([^/]) that are not slashes, at the end of the string ($).

--------------- MYSQL 

with cte as (

select 'c:/home/user/sreenu/test/emp.txt' as filename from dual union all 

select 'd:home/dept.txt' as filename from dual union all 

select 'e:/salgrade.csv' as filename from dual union all 

select 'c:/home/user/test1.txt' as filename from dual 

)

select 

REGEXP_SUBSTR(filename, '[^/]+$') file1, instr(filename,'/'),

reverse(substr(reverse(filename),1,instr(reverse(filename),'/')-1)) file2,

REGEXP_SUBSTR(filename, '[^/]+$') file3,

SUBSTRING_INDEX(filename, '/', -1) file4

 

from cte;

---------------SQL SERVER
with cte as (

select 'c:/home/user/sreenu/test/emp.txt' as filename  union all

select 'd:home/dept.txt' union all

select 'e:/salgrade.csv' union all

select 'c:/home/user/test1.txt'

)

select

REVERSE(SUBSTRING(REVERSE(filename), 1, CHARINDEX('/', REVERSE(filename) + '/') - 1)) AS File_Name

from cte;

--------------- POSTGRESQL

with cte as (

select 'c:/home/user/sreenu/test/emp.txt' as filename  union all

select 'd:home/dept.txt' union all

select 'e:/salgrade.csv' union all

select 'c:/home/user/test1.txt'

)

select

REGEXP_REPLACE(filename, '.*[\\/]', '') AS file_name,

split_part(filename, '/', array_length(string_to_array(filename, '/'), 1)) AS file_name

from cte;

 Explanation:

The VALUES clause is used to simulate a table of your input file paths.

The REGEXP_REPLACE function is used to replace everything up to and including the last / or \ with an empty string, leaving just the file name.

If you're looking for more simple alternatives or if you want to ensure compatibility without regex, you could also use the split_part function like this: