转载

DB String Split sample

以sqlserver为例进行说明，代码稍加修改，可以用于ORACLE。

字符串的拆分和聚合是数据库开发人员比较常见的任务。

关于字符串的聚合，sqlserver的标准解决方案是for xml path. oracle 11g提供了listagg函数方便获取。

今天就字符串的拆分进行分析。重点在于思路。

示例数据.

1,’a,b,c,d,e,f,g’

需要把数据拆分成标准的行。

1，a

1,b

第一步：

一行变多行，需要增加副本，如何增加条数？而且条数刚好和分隔符的个数+1个相同。

可以使用数据辅助表 join 原表，需要注意的是如何写on的条件，来达到要求。

第二步：只要办成了第一步，第二步就简单明了了，截取字符串，如何截取合适的长度？

有了思路，就可以写出代码，细节处再慢慢调整即可。以下写入我的代码。

第二种思路是使用递归CTE。

第一步写基准查询，查出每一个待拆分的字符串中第一个分隔符出现的位置，作为截取字符串的结束位置，而

显然1是开始位置。

第二步递归步骤，根据第一步，[end]+1 as start, 再把第一个的[end+1]作为查询的起点，查找下一个分隔符出现的位置作为结束位置。

结束条件就是从N开始查找待分隔的字符串没有分隔符了。

方法二写起来简单明了，但是如果待分隔的字符串过长，则递归的次数会容易超过数据库默认的递归最大次数，会报错。而且大量的递归

消费内存和执行效率不高，慎用。sqlserver中可以用hintOPTION(MAXRECURSION N)来处理这个问题。

--USE TEMPDB; --GO --CREATE TABLE MYTEST(ID INT,NAMELIST VARCHAR(200)); --INSERT INTO MYTEST VALUES(1,'A,B,C,D,E,F,G,H,I,J,K'); --INSERT INTO MYTEST VALUES(2,'X,Y,Z'); --INSERT INTO MYTEST VALUES(3,'QQ'); --SELECT * FROM MYTEST;

WITH NUMS(N) AS( SELECT ROW_NUMBER() OVER(ORDER BY OBJECT_ID) AS N FROM SYS.ALL_OBJECTS ) SELECT  ID, SUBSTRING( NAMELIST, NUMS.N, CHARINDEX(',',NAMELIST+',',N)-N ) AS NAME FROM MYTEST JOIN NUMS ON LEN(MYTEST.NAMELIST)>NUMS.N-1 AND SUBSTRING(','+MYTEST.NAMELIST+',',N,1)=',' ORDER BY ID, NAMELIST ,N

CTE方法拆分字符串的示例代码：

--CTE WITH CTE AS (  SELECT ID, 1 AS [START],CHARINDEX(',',NAMELIST+',') AS [END],NAMELIST FROM MYTEST  UNION ALL  SELECT ID,[END]+1 AS START,CHARINDEX(',',NAMELIST+',',[END]+1) AS [END],NAMELIST FROM CTE  WHERE CHARINDEX(',',NAMELIST+',',[END]+1)>0 ) SELECT ID,SUBSTRING(NAMELIST,START,[END]-[START]) AS [NAME] FROM CTE ORDER BY ID,NAME OPTION(MAXRECURSION  1000);