How to get file extension from filename in postgresql?

by dalton_moen , in category: MySQL , 4 months ago

How to get file extension from filename in postgresql?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

Member

by aubrey , 4 months ago

@dalton_moen 

You can get the file extension from a filename in PostgreSQL by using the substring function along with regular expressions. Here is an example query:

1
SELECT substring('example.txt' from '.(w+)$') as file_extension;


In this query, 'example.txt' is the filename from which you want to extract the file extension. The regular expression '.(w+)$' is used to match the last occurrence of a period followed by one or more word characters at the end of the filename, which represents the file extension. The substring function then extracts and returns the matched file extension.


This query will return the file extension of the given filename, in this case, it will return txt.