CODEWITHSUNDEEP
postgresqlfileplpgsqlfile-extensionphp-pgsql
Vijayaragavan Ambalam
Vijayaragavan Ambalam

Reputation: 3

How to extract file extension from filepath field in PostgreSQL?

need to extract file extensions from the file_path column from my PostgreSQL table. kindly help me to achieve to extract the distinct file extensions as list using select query.

have table like this

id file_path
1 https://example.com/uploads/profile_pic.jpeg
2 https://example.com/public/upload/resume.png

expected output like is

row_no extension
1 jpeg
2 png

my query

select reverse(substring(reverse(file_path) from 1 for strpos(reverse(file_path),'.')-1)) as extension from pgsql_table_files;

Upvotes: 0

Views: 134

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19665

A simple solution using split_part from here String Functions.

create table file_ext(id integer, file_path varchar);
insert into file_ext values (1, 'https://example.com/uploads/profile_pic.jpeg'), 
(2, 'https://example.com/public/upload/resume.png'), 
(3, 'https://example.com/public/upload/cover_letter.png'), 
(4, 'https://example.com/uploads/cat_pic.jpeg');

select * from file_ext ;
 id |                     file_path                      
----+----------------------------------------------------
  1 | https://example.com/uploads/profile_pic.jpeg
  2 | https://example.com/public/upload/resume.png
  3 | https://example.com/public/upload/cover_letter.png
  4 | https://example.com/uploads/cat_pic.jpeg


select distinct split_part(file_path , '.', -1) as extension from file_ext;
 extension
------------
 png
 jpeg

Use split_part to break the string on . and then use reverse indexing(-1) to fetch the last split which is the extension. Then use DISTINCT to fetch only one record for each extension.

If you want to include a row number then:

select 
   distinct on(split_part(file_path , '.', -1)) row_number() over() as row_no, 
   split_part(file_path , '.', -1) as extension from file_ext;

 row_no | extension 
--------+------------
      1 | jpeg
      2 | png

Upvotes: 1

Related Questions