I have data in below format in an Oracle table column: I want to have data in below format i.e to have distinct first string after first occurrence of :: I can get the value for first record using below query But I am unable to get it for others if there are more than one : in a column

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

​x
 
ABC:XYZABC:MNO:LMOABC:MNO:LRT:RNOPQR:TYU:MNO​

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

 XYZ
 MNO
 TYU

 
 XYZ MNO TYU​

I can get the value for first record using below query

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

 
 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.

You can use two more arguments:

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

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

Here is a db<>fiddle.