Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 12.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


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');

CREATE TABLE



INSERT 0 9


--
-- My first answer - note it fails on file7 (no extension) and on file8 - the .tar
-- is not present
--

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;


my_file


file1

file2

file3

file4

file5

file6

file

file8

my_prog



SELECT 9


--
-- @DavidBoho 's solution using SPLIT_PART
--

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


filename


file1

file2

file3

file4

file5

file6

file7

file8.tar

my_prog



SELECT 9


--
--  2nd Tradtional SQL (mine) - works with all files in test
--

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


the_files


file1

file2

file3

file4

file5

file6

file7

file8.tar

my_prog



SELECT 9


--
-- 1st pass with regex - note file7 - no extension
--

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


file_name
filename


/users/mcm1/ualaoip2/vmm/file1.pdf
file1

/users/mcm1/ualaoip2/vmm/file2.py
file2

/users/mcm1/ualaoip2/vmm/file3.pdf
file3

/users/mcm1/ualaoip2/vmm/file4.c
file4

/users/mcm1/ualaoip2/vmm/file5.java
file5

/users/mcm1/ualaoip2/vmm/file6.class
file6

/users/mcm1/ualaoip2/vmm/file7
/users/mcm1/ualaoip2/vmm/file7

/users/mcm1/ualaoip2/vmm/file8.tar.gz
file8.tar

/users/mcm1/my_prog.cpp
my_prog



SELECT 9


--
-- A regex solution - works for everything
--

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


file_name
filename


/users/mcm1/ualaoip2/vmm/file1.pdf
file1

/users/mcm1/ualaoip2/vmm/file2.py
file2

/users/mcm1/ualaoip2/vmm/file3.pdf
file3

/users/mcm1/ualaoip2/vmm/file4.c
file4

/users/mcm1/ualaoip2/vmm/file5.java
file5

/users/mcm1/ualaoip2/vmm/file6.class
file6

/users/mcm1/ualaoip2/vmm/file7
file7

/users/mcm1/ualaoip2/vmm/file8.tar.gz
file8.tar

/users/mcm1/my_prog.cpp
my_prog



SELECT 9


--
-- Another regex solution - note however the "hack" of the double REGEXP_REPLACE
--

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


file_name
filename


/users/mcm1/ualaoip2/vmm/file1.pdf
file1

/users/mcm1/ualaoip2/vmm/file2.py
file2

/users/mcm1/ualaoip2/vmm/file3.pdf
file3

/users/mcm1/ualaoip2/vmm/file4.c
file4

/users/mcm1/ualaoip2/vmm/file5.java
file5

/users/mcm1/ualaoip2/vmm/file6.class
file6

/users/mcm1/ualaoip2/vmm/file7
file7

/users/mcm1/ualaoip2/vmm/file8.tar.gz
file8.tar

/users/mcm1/my_prog.cpp
my_prog



SELECT 9


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;



fn
lef


file7
file

file1.pdf
file1

file2.py
file2

file3.pdf
file3

file4.c
file4

file5.java
file5

file6.class
file6

file8.tar.gz
file8

my_prog.cpp
my_prog



SELECT 9


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


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



SELECT 9

file_name	filename
/users/mcm1/ualaoip2/vmm/file1.pdf	file1
/users/mcm1/ualaoip2/vmm/file2.py	file2
/users/mcm1/ualaoip2/vmm/file3.pdf	file3
/users/mcm1/ualaoip2/vmm/file4.c	file4
/users/mcm1/ualaoip2/vmm/file5.java	file5
/users/mcm1/ualaoip2/vmm/file6.class	file6
/users/mcm1/ualaoip2/vmm/file7	/users/mcm1/ualaoip2/vmm/file7
/users/mcm1/ualaoip2/vmm/file8.tar.gz	file8.tar
/users/mcm1/my_prog.cpp	my_prog

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