How to get substring from 4th occurence of a character until the end of given string in PSQL

Question

Example

I have a string...

'/this/is/a/given/string/test.file'.

How can I get substring 'given/string/test.file' in PSQL?

Thank you!

Answer 1

You can use a regular expression

with example(str) as (
    values('/this/is/a/given/string/test.file')
)

select regexp_replace(str, '(/.*?){4}', '')
from example;

     regexp_replace     
------------------------
 given/string/test.file
(1 row) 

or the function string_to_array():

select string_agg(word, '/' order by ord)
from example,
unnest(string_to_array(str, '/')) with ordinality as u(word, ord)
where ord > 4;

Read also How to find the 3rd occurrence of a pattern on a line.

Answer 2

I dont know how to get the nth occurence of a substring, but for this problem, you can use regular expression. Like this:

select substring('/this/is/a/given/string/test.file' from '/[^/]+/[^/]+/[^/]+/(.*)')

You can improve the regular expression, this is just for demo purpose.