how to split a string which is having comma and colon-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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

REPLACED_VALUE
SARAH,JOE,KANE,SDF

db<>fiddle here