Try this..
SELECT COLUMN_1 FROM(
SELECT REGEXP_SUBSTR(
'ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)','[^~]+', 1, level)
AS COLUMN_1
FROM DUAL
CONNECT BY REGEXP_SUBSTR('ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)',
'[^~]+', 1, LEVEL) IS NOT NULL
ORDER BY ROWNUM DESC) WHERE ROWNUM = 1 ;
OR
SELECT substr('ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)',
instr('ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)','~',1,
regexp_count('ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)','~'))+1)
FROM dual;