新建一个function 实现此功能
create or replace FUNCTION SF_SPLIT_STR(strName IN VARCHAR2)
RETURN VARCHAR2 AS
v_strName VARCHAR2(5000);
BEGIN
WITH ACCTS AS
(SELECT DISTINCT str
from (SELECT REGEXP_SUBSTR(replace(strName, ', ', ','),
'[^,]+',
1,
LEVEL,
'i') AS STR
FROM DUAL
CONNECT BY LEVEL <= LENGTH(replace(strName , ', ', ',')) -
LENGTH(REGEXP_REPLACE(replace(strName ,
', ',
','),
',',
'')) + 1))
SELECT wm_concat(STR) INTO v_strName FROM ACCTS;
RETURN v_strName;
END SF_SPLIT_STR;