数据库中分割符号转为多行

--思路1：先创建一个递增序列，保证对应行号下有对应条行数据，
--统计需分割字符串中个数，关联递增序列，实现1行转多行
--获取位置，截取字符串
--思路2：xml处理，符号替换，创建函数查询
--思路3：递归查询
1 --oracle,分割逗号
2 --创建表
3 CREATE TABLE SPLIT_R(ID VARCHAR2(2000),SEQ NUMBER)
4 --插入测试数据
5 INSERT INTO SPLIT_R(SEQ,ID) VALUES(1,‘A,B‘);
6 INSERT INTO SPLIT_R(SEQ,ID) VALUES(2,‘A‘);
7 INSERT INTO SPLIT_R(SEQ,ID) VALUES(3,‘A,B,C‘);
8 INSERT INTO SPLIT_R(SEQ,ID) VALUES(4,‘A,B,C,D‘);

1 INSERT INTO SPLIT_R(SEQ,ID) VALUES(4,‘AB,BBBB,CCC,DDDD‘);

1 --解决方案1：
2 --1、新建序列
3 WITH A AS (SELECT ROWNUM AS NM FROM DUAL CONNECT BY ROWNUM<100)
4 SELECT * FROM A LEFT JOIN A B ON A.NM>B.NM

 1 --查询
 2 WITH A AS (SELECT ROWNUM AS nm from dual connect by rownum<100),
 3 B AS (select A.NM,B.NM CNM from a left JOIN a b on a.nm>=b.nm),
 4 C AS (SELECT REGEXP_COUNT(ID,‘[,]+‘)+1 CNT,A.ID||‘,‘ AS ID,ID AS ID1,A.SEQ FROM SPLIT_R A)
 5 SELECT c.ID1,SEQ,
 6 --INSTR(ID,‘,‘,1,CNM),CASE WHEN CNM=1 THEN 1 ELSE INSTR(ID,‘,‘,1,CNM-1)+1 END AS ST,
 7 --INSTR(ID,‘,‘,1,CNM)-(CASE WHEN CNM=1 THEN 1 ELSE INSTR(ID,‘,‘,1,CNM-1)+1 END) ET ,
 8 SUBSTR(ID,(CASE WHEN CNM=1 THEN 1 ELSE INSTR(ID,‘,‘,1,CNM-1)+1 END),(INSTR(ID,‘,‘,1,CNM)-(CASE WHEN CNM=1 THEN 1 ELSE INSTR(ID,‘,‘,1,CNM-1)+1 END))) STR
 9 FROM B LEFT JOIN C ON B.NM= C.CNT  
10 WHERE CNT IS NOT NULL
11 ORDER BY C.SEQ,B.CNM