how to split a string which is having comma and colon

Question

I have a following query like this

 SELECT REGEXP_SUBSTR('SARAH;10,JOE;1D,KANE;1A,SDF:1a', '[^,;]+', 1, level)
   FROM dual
CONNECT BY REGEXP_SUBSTR('SARAH;10,JOE;1D,KANE;1A,SDF:1a',
                         '[^,;]+',
                         1,
                         level) IS NOT NULL;

I am trying to get o/p as SARAH,JOE,KANE,SDF

Answer 1

If there's only one row of data, then you can use

WITH t(str) AS
(
 SELECT 'SARAH;10,JOE;1D,KANE;1A,SDF:1a' FROM dual
), t2 AS
(
 SELECT level AS lvl, REGEXP_SUBSTR(str, '[^,;:]+', 1, level) AS str
   FROM t
 CONNECT BY REGEXP_SUBSTR(str,
                          '[^,;]+',
                          1,
                          level) IS NOT NULL
)
SELECT LISTAGG(str,',') WITHIN GROUP (ORDER BY lvl) AS result
  FROM t2  
 WHERE NOT REGEXP_LIKE(str,'^(\d)')                         

in order to filter the extracted substrings which don't start with an integer through use of REGEXP_LIKE() like above

Answer 2

Don't split the string and re-aggregate. Just replace the string from each ; or : until the next , or then end-of-the-string:

SELECT REGEXP_REPLACE(
         'SARAH;10,JOE;1D,KANE;1A,SDF:1a',
         '[;:].*?(,|$)',
         '\1'
       ) AS replaced_value
FROM   DUAL;

Which outputs:

REPLACED_VALUE
SARAH,JOE,KANE,SDF

db<>fiddle here

---

Update

If your delimiter can be any one of the ;:, characters until the next ;:, character or the end-of-the-string then:

SELECT value,
       RTRIM(REGEXP_REPLACE(value, '[;:,].*?([;:,]|$)', ','), ',')
         AS replaced_value
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'SARAH;10,JOE;1D,KANE;1A,SDF:1a' FROM DUAL UNION ALL
SELECT 'SARAH,10;JOE,1D;KANE,1A;SDF:1a' FROM DUAL;

Outputs:

VALUE	REPLACED_VALUE
SARAH;10,JOE;1D,KANE;1A,SDF:1a	SARAH,JOE,KANE,SDF
SARAH,10;JOE,1D;KANE,1A;SDF:1a	SARAH,JOE,KANE,SDF

db<>fiddle here