SELECT regexp_substr(col,'[^|]+',1,1),regexp_substr(col,'[^|]+',1,2),regexp_substr(col,'[^|]+',1,3),regexp_substr(col,'[^|]+',1,4) FROM
( SELECT regexp_substr('aaa|bbb|ccc|ddd,cccc|ddd|aaaa|fffff','[^,]+',1,1) col FROM dual
UNION ALL
SELECT regexp_substr('aaa|bbb|ccc|ddd,cccc|ddd|aaaa|fffff','[^,]+',1,2) FROM dual
)
死办法,如果个数不确定,就麻烦了
( SELECT regexp_substr('aaa|bbb|ccc|ddd,cccc|ddd|aaaa|fffff','[^,]+',1,1) col FROM dual
UNION ALL
SELECT regexp_substr('aaa|bbb|ccc|ddd,cccc|ddd|aaaa|fffff','[^,]+',1,2) FROM dual
)
死办法,如果个数不确定,就麻烦了
你自己再简单的修改一下即可
with t as
(select 'aaa|bbb|ccc|ddd,cccc|ddd|aaaa|fffff' book_nm from dual)select rn, REGEXP_SUBSTR(str, '[^|]+', 1, LEVEL)
from (select rownum rn, REGEXP_SUBSTR(book_nm, '[^,]+', 1, LEVEL) STR
from (select rownum id, t.* from t) t1
connect by level <=
length(book_nm) - length(replace(book_nm, ',', '')) + 1
and id = prior id
and prior dbms_random.value is not null) t2
connect by level <= length(str) - length(replace(str, '|', '')) + 1
and rn = prior rn
and prior dbms_random.value is not null;