SQL & PL/SQL

Hi,

I need to match all 3 patterns below:

:XXXXXXX/ or -- the string is between a colon and a forward slash

xxxxxx/ or -- the string that is before the backslash when there's no colon at the beginning.

:xxxxxxxx or the string that is after the colon when there's no backslash

CREATE TABLE REG_EXP_TEST (KEY_VALUE VARCHAR2(100));

INSERT INTO REG_EXP_TEST VALUES ('ARCHIVE_01/TGITT7_ARCH');

INSERT INTO REG_EXP_TEST VALUES ('POOL01A:ACEMT-MT01_12049_SWAP_CON/ACEMT-MT01_SWAP_STOR');

INSERT INTO REG_EXP_TEST VALUES ('POOL04A:DEFAULT');

COMMIT;

This is the sql I'm using but it matches only the first and second case and is ignoring the third case (:xxxxxxxx).  Is there a way that it can match these 3 patterns?.  In red is the string that I need to match.

SELECT KEY_VALUE, REGEXP_SUBSTR(KEY_VALUE,'[^:]+(?/)',1,1,'i') as project

FROM REG_EXP_TEST;

----------------------------------------------------------                                                           ----------------------------

Thanks for your help.