4

I'm trying to get a filename contained in the value of a specific column of my table. My table looks like this:

absolutel_path
\\Path\filename.extension

I need to extract the filename (in the above filename) from the absolute_path (\\Path\filename.extension). Which function should I use to get my filename (substring) out?

1
  • 4
    Which RDBMS server are you using?
    – Vérace
    Nov 15, 2017 at 12:19

2 Answers 2

8

Edit:

Even though my first solution answered the question as asked, I saw @DavidBoho 's answer and he made several good points. He suggested that if the filename is my_file.tar.gz then the return value should be my_file.tar and also that my solution would fail in the event that the file had no extension at all. All of the code here is available on this fiddle.

Given the table and data as follows:

CREATE TABLE with_filename
(
  file_id INTEGER,
  file_name VARCHAR (256)
);

populate:

INSERT INTO with_filename
VALUES
(1, '/users/mcm1/ualaoip2/vmm/file1.pdf'),
(2, '/users/mcm1/ualaoip2/vmm/file2.py'),
(3, '/users/mcm1/ualaoip2/vmm/file3.pdf'),
(4, '/users/mcm1/ualaoip2/vmm/file4.c'),
(5, '/users/mcm1/ualaoip2/vmm/file5.java'),
(6, '/users/mcm1/ualaoip2/vmm/file6.class'),
(7, '/users/mcm1/ualaoip2/vmm/file7'),
(8, '/users/mcm1/ualaoip2/vmm/file8.tar.gz'),
(9, '/users/mcm1/my_prog.cpp');

My original solution:

SELECT LEFT(
      RIGHT(file_name, POSITION('/' IN REVERSE(file_name)) - 1),
                       POSITION('.' IN 
      RIGHT(file_name, POSITION('/' IN REVERSE(file_name)) - 1)) - 1
) AS my_file
FROM with_filename;

gives the result:

my_file
file1
file2
file3
file4
file5
file6
file       -- << should be file7
file8      -- << should be file8.tar
my_prog

After reading @DavidBoho's post, he used the SPLIT_PART function to resolve the problems with files 7 & 8 - see the fiddle. I decided to look again at my own SQL and I came up with this (perhaps more traditional?):

SELECT 
      REPLACE(SUBSTRING(file_name, (LENGTH(file_name) + 2) - POSITION('/' IN REVERSE(file_name))),
      RIGHT(file_name, POSITION('.' IN LEFT(REVERSE(file_name), POSITION('/' IN REVERSE(file_name)) - 1))),
      '') AS the_files
    FROM with_filename

Result:

the_files
file1
file2
file3
file4
file5
file6
file7
file8.tar
my_prog

which is also the correct answer!

While I was grasping for a solution, I became interested in regular expressions as a means of solving this problem. Even though I (we) was/were able to solve this using "traditional" SQL, it became clear to me that regexes are extremely powerful and even though SQL is now Turing complete, it could rapidly become extremely convoluted for relatively simple string manipulation problems, so I decided to investigate.

I found two regex solutions - in fairness, I can't claim to have done this myself, the solutions are as a result of a question I asked on StackOverflow. So, the regex solutions are as follows:

The best one is this one -

SELECT
    file_name,
    REGEXP_REPLACE(file_name, '^.*/([^/]*?)(\.[^/.]+)?$', '\1') AS filename
FROM with_filename;

There is a second one, but IMHO (and that of the original author) it's a bit of a hack - it involves two nested REGEXP_REPLACEs

SELECT
    file_name,
    REGEXP_REPLACE(REGEXP_REPLACE(file_name, '^.*/(.*)$', '\1'), '\.[^.]+$', '') AS filename
FROM with_filename

Finally, there may be a solution possible using the UNNEST and the STRING_TO_ARRAY functions together - I came up with this code:

SELECT fn,  
    LEFT(fn, POSITION('.' IN fn) - 1) AS lef
FROM with_filename w,
  UNNEST(STRING_TO_ARRAY(w.file_name, '/')) AS fn
GROUP BY fn
HAVING COUNT(fn) = 1
ORDER BY lef;

which gives the result:

fn  lef
file7   file       -- << should be file7
file1.pdf   file1
file2.py    file2
file3.pdf   file3
file4.c     file4
file5.java  file5
file6.class     file6
file8.tar.gz    file8  -- << should be file8.tar
my_prog.cpp     my_prog

I tried lots of different permutations with this, but couldn't get it to work. Would be grateful for any input! :-)

Another interesting function is REGEXP_SPLIT_TO_TABLE.

SELECT 
  fn, 
  COUNT(fn)
FROM
(
  SELECT REGEXP_SPLIT_TO_TABLE(w.file_name, '/') AS fn
  FROM with_filename w
) AS sq
GROUP BY fn
HAVING COUNT(fn) = 1
ORDER BY fn

Result:

fn  count
file1.pdf   1
file2.py    1
file3.pdf   1
file4.c     1
file5.java  1
file6.class     1
file7   1
file8.tar.gz    1
my_prog.cpp     1

Again, this might be worth pursuing - didn't have time.

Example DML/DDL

CREATE TABLE with_filename
(
  file_id INTEGER,
  file_name VARCHAR (256)
);

INSERT INTO with_filename
VALUES
(1, '/users/mcm1/ualaoip2/vmm/file1.pdf'),
(2, '/users/mcm1/ualaoip2/vmm/file2.py'),
(3, '/users/mcm1/ualaoip2/vmm/file3.pdf'),
(4, '/users/mcm1/ualaoip2/vmm/file4.c'),
(5, '/users/mcm1/ualaoip2/vmm/file5.java'),
(6, '/users/mcm1/ualaoip2/vmm/file6.class'),
(7, '/users/mcm1/ualaoip2/vmm/file7'),
(8, '/users/mcm1/ualaoip2/vmm/file8.tar.gz'),
(9, '/users/mcm1/my_prog.cpp');
1
  • I tried to explain this a little better feel free to roll it back if you don't approve. Nov 18, 2017 at 16:40
1

I'm a little late to this party, but I will also share my solution that will also handle some corner cases, that are not covered by the answer of Vérace.

There are two concepts for filename without extension:

  1. without last extension filename.tar.gzfilename.tar (common concept of filename)
  2. without any extension filename.tar.gzfilename

In all implementations I have found so far, filename refers to the first concept. So only the last part is treated as extension and the rest as filename.

The solution of Vérace instead implemented the second concept and his solution also fail in two corner cases:

  • no directory in the path filename.tar.gz
  • no extension in the path filename

There are also some other cases that you should keep in mind for a possible solution:

  • dots in directory names or relative paths e.g.: ../filename-without-ext

You can find both implementations in the DB Fiddle here...

Solution common concept dir/filename.tar.gzfilename.tar

 SELECT LEFT(
                -- The last path segment a.k.a. basename
                REVERSE(SPLIT_PART(REVERSE(path), '/', 1)),
                -- The length of the basename
                LENGTH(SPLIT_PART(REVERSE(path), '/', 1))
                    -- The length of the extension (with dot) in the basename
                    - STRPOS(SPLIT_PART(REVERSE(path), '/', 1), '.')
            ) as filename
 FROM samples

The solution in detail. At first we get the last segment of the path (a.k.a. basename) by this combination:

REVERSE(SPLIT_PART(REVERSE(path), '/', 1))

Next we will calculate the length of the filename to strip this of with LEFT to the basename. So we subtract the length of the basename with the length of the extension and the dot. The length of the extension including the dot is the same as the position of the dot from the right.

LENGTH(basename) - STRPOS(REVERSE(basename), '.')

Remark: There are still two corner cases that are not covered by this solution. But this two cases will also handled differently in common implementations.

  1. The current dir and parent dir "filename" dir/. and dir/..

In C++ stem
fs::path("dir/.").stem().
fs::path("dir/..").stem()..

In PHP pathinfo
pathinfo('dir/.', PATHINFO_FILENAME)
pathinfo('dir/..', PATHINFO_FILENAME).

  1. "hidden" files e.g.: .profile

In C++ stem
fs::path("dir/.profile").stem().profile

In PHP pathinfo
pathinfo('dir/.profile', PATHINFO_FILENAME)

Not common concept solution dir/filename.tar.gzfilename

Just for completeness here is a solution without any extensions:

SPLIT_PART(REVERSE(SPLIT_PART(REVERSE(path), '/', 1)), '.', 1)
1
  • In the light of your answer, I considerably revised my own one. I'd be interested in your comment - I tried to take on board the points you made - I also upvoted your answer because, a) it was good and b) I learnt a shedload revising my own.
    – Vérace
    May 7, 2020 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.