Postgres - substring from the beginning to the second last occurrence of a char within a string-CodePudding

I need to retrieve the bolded section of the below string . This value is in a column within my Postgres database table.

SEALS_LME_TRADES_MBL_20220919_00212.csv

I tried to utilize the functions; substring, reverse, strpos but they all have limitations. It seems like regex is the best option, however I was not able to do it.

Essentially I need to substring from beginning till the second last '_'. I do not want the date and sequence number along with the file extension at the end.

The closes regex I managed to get is: ^(([^]*){4}) https://regex101.com/

CodePudding user response：

This look a little wonky but how about this?

select substring ('SEALS_LME_TRADES_MBL_20220919_00212.csv', '^(. )_[^_] _[^_] ')

Translation

^     from the beginning
(. )  any characters (capture and return this value), followed by
_     an underscore, followed by
[^_]  one or more non-underscores, followed by
_     an underscore, followed by
[^_]  one or more non-underscores

Regex greediness will cause any incidental underscores to be captured in the initial string.

Technically speaking the last portion (one or more non-underscores) can probably be omitted.