How to get the part of a string after the last occurrence of certain character?

I would like to have the substring after the last occurrence of a certin character. Now I found here how to get the first, second or so parts, but I need only the last part. The input data is a list of file directories:

c:dirsubdirsubdirfile.txt
c:dirsubdirsubdirfile2.dat
c:dirsubdirfile3.png
c:dirsubdirsubdirsubdirfile4.txt

​x
 
c:dirsubdirsubdirfile.txtc:dirsubdirsubdirfile2.datc:dirsubdirfile3.pngc:dirsubdirsubdirsubdirfile4.txt​

Unfortunately this is the data I have to work it, otherwise I could list it using command prompt. The problem is that the number of the directories are always changing. My code based on the previous link is:

select (regexp_split_to_array(BTRIM(path),'\'))[1] from myschema.mytable

 
select (regexp_split_to_array(BTRIM(path),'\'))[1] from myschema.mytable​

So far I’ve tried some things in the brackets that came in to my mind. For example [end], [-1] etc. Non of them are working. Is there a way to get the last part without rearranging my strings backwards, and getting the first part, then turning it back?

Answer

You can use regexp_matches():

select (regexp_matches(path, '[^\]+$'))[1]

 
select (regexp_matches(path, '[^\]+$'))[1]​

Here is a db<>fiddle.

Advertisement

Answer