Substring to Get value only after first occurrence of : in Oracle SQL

I have data in below format in an Oracle table column:

ABC:XYZ
ABC:MNO:LMO
ABC:MNO:LRT:RNO
PQR:TYU:MNO

I want to have data in below format i.e to have distinct first string after first occurrence of ::

 XYZ
 MNO
 TYU

I can get the value for first record using below query

 select REGEXP_SUBSTR('ABC:XYZ','[^:]+$') from dual

But I am unable to get it for others if there are more than one : in a column value.

Any guidance would be great.

Answer

You can use two more arguments:

select REGEXP_SUBSTR('ABC:XYZ', '[^:]+', 1, 2)
from dual;

Here is a db<>fiddle.