Regular expression/ Redshift

Question

I have following data, how do i find 11th occurrence of ':' . I want to print/display the information after 11th occurrence of ':'.

https://www.example.com/rest/1/07/myself/urn:ads:accod:org:pki:71E4/Riken/List:abc:bcbc:hfhhf:ncnnc:shiv:hgh:bvbv:hghg:

I have tried [^] tag but its not working.

select regexp_substr(id,'[:]{5}?.*') from tempnew;

Answer 1

you can use split_part for this purpose, select split_part(id, ':', 12) from tempnew

Answer 2

regexp_substr does not care about capture-groups, so counting characters not included in the match is not possible. Counting from the end would work though:

-- Returns the substring after the 6th ':' from the end.
select regexp_substr(id, '([^:]*:){5}[^:]*$') from tempnew
-- If the string does not contain 5 ':', an empty string is returned.

If you need to count from the start, you could use regexp_replace instead:

-- Returns the substring after the 11th ':'
select regexp_replace(id, '^([^:]*:){11}') from tempnew
-- If the string does not contain 11 ':', the whole string is returned.

Answer 3

I would split on ":" and use the 11th element.

But if you must use a regex:

^(?:[^:]*:){10}:([^:]*)

And use group 1 of the match.

Answer 4

see this demo https://regex101.com/r/wR9aU3/1

/^(?:[^:]*\:){11}(.*)$/

or

/^(?:.+\:){11}(.+)$/gm

https://regex101.com/r/oC5yQ6/1