How to get file extension from filename in PostgreSQL?

Question

Using PostgreSQL 9.3. Have a column with file name values like that:

qwerty.swf
some.image.jpg
some.other.image.jpg 

This column also allow null values.

How to get file extensions from this column in sql query?

Towney

One filename per row, or comma separated? – Berkeley

one filename per row – Towney

Answer 1

try :

    with mytable as (
    select unnest(string_to_array($$qwerty.swf
    some.image.jpg
    some.other.image.jpg
    test
    $$,E'\n')) filename)

    select filename,substring(filename from '\.([^\.]*)$') 
    from mytable

Caulis

Answer 2

Use regexp to do this.

select regexp_matches(filename,'\.(\w+)$')
from tablename
where filename ~ '\.' --check if filename has atleast 1 . character in it

Sample fiddle

Or a combination of substring, reverse and strpos.

select reverse(substring(reverse(filename) from 1 for strpos(reverse(filename),'.')-1))
from tablename
where filename ~ '\.' --check if filename has atleast 1 . character in it

Leveller

Be carreful it's dangerous : regexp_matches delete rows if pattern doesn't match. – Sculpin

It is not true that regexp_matches deletes any rows. This function isn't even passed a table name, let alone the primary key of a record to delete.If you want to delete rows, either use the DELETE statement, or the TRUNCATE statement. – Breazeale

Answer 3

In case you want to extract the extension of a file from an URL, the thing is slightly more difficult, as a short URL such as https://whatever.com could be taken for a file descriptor, resulting in com as an extension.

This is my approach.

create or replace function url_ext (s varchar)
returns varchar
immutable language sql as $$
  select substring(s from '^http[s]?:\/\/.*\/.*\.([A-z0-9_-]*)$');
$$;

And these are some tests:

select 
    url_ext('http://whatever.com'),
    url_ext('https://whatever.com'),
    url_ext('https://whatever.com/something/more/file.pdf'),
    url_ext('https://whatever.com/file.pdf'),
    url_ext('https://whatever.com/something/more/slug');

url_ext|url_ext|url_ext|url_ext|url_ext|
-------+-------+-------+-------+-------+
       |       |pdf    |pdf    |       |

Dun