--oracle 数据库拆分办法
WITH TT(bbbh,
USER_,
sjly) AS
(SELECT 00 bbbh, '' USER_, 'a^b^c' || '^' sjly
from dual
UNION ALL
SELECT bbbh,
SUBSTR(TT.sjly, 1, Instr(TT.sjly, '^') - 1),
SUBSTR(TT.sjly, Instr(TT.sjly, '^') + 1)
from TT
WHERE LENGTH(TT.sjly) > 1)
SELECT bbbh,
substr(TRIM(USER_), Instr(TRIM(USER_), ',') + 1, length(USER_)) fy
FROM TT
WHERE USER_ is not null
ORDER BY USER_, bbbh
--db2 数据库拆分办法
WITH N(STRING,ORI,POS) AS (
VALUES('AAA,BBB,CCC,DDD,EEE,',1,POSSTR('AAA,BBB,CCC,DDD,EEE,',','))
UNION ALL
SELECT STRING,POS+1,LOCATE(',',STRING,POS+1) FROM N
WHERE LOCATE(',',STRING,POS+1)>0
)
SELECT STRING,ORI,POS,SUBSTR(STRING,ORI,POS-ORI) FROM N