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

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

SELECT * FROM SPLIT_R

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

 

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

 

--查询
WITH A AS (SELECT ROWNUM AS nm from dual connect by rownum<100),
B AS (select A.NM,B.NM CNM from a left JOIN a b on a.nm>=b.nm),
C AS (SELECT REGEXP_COUNT(ID,'[,]+')+1 CNT,A.ID||',' AS ID,ID AS ID1,A.SEQ FROM SPLIT_R A)
SELECT c.ID1,SEQ,
--INSTR(ID,',',1,CNM),CASE WHEN CNM=1 THEN 1 ELSE INSTR(ID,',',1,CNM-1)+1 END AS ST,
--INSTR(ID,',',1,CNM)-(CASE WHEN CNM=1 THEN 1 ELSE INSTR(ID,',',1,CNM-1)+1 END) ET ,
--REGEXP_SUBSTR(ID,'[^,]+',1，CNM),

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
FROM B LEFT JOIN C ON B.NM= C.CNT  
WHERE CNT IS NOT NULL
ORDER BY C.SEQ,B.CNM