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);

由于oracle和sqlserver部分字符串函数实现不同,用oracle的函数可以写出更优雅简单的方法。欢迎大家尝试。

posted on 2015-01-14 21:22  tneduts  阅读(782)  评论(1编辑  收藏  举报

导航