求sql拆分字符串的通用算法

[xiedi1209]

我现在有一个字符串"1,2,3,4,(5,6),7,(8,(9,10))" 要统计,的个数
按常规 我想按 , 拆成7个
1
2
3
4
(5,6)
7
(8,(9,10))
然后统计 , 的个数 本来这样 , 的个数应该是6个 就是说把(5,6),(8,(9,10))看成一体
不过我只会统计9个出来……
请问怎么解决 最好是标准sql，而其烦心的是这个括号还可能更多

[qgylovelj]

hen qiang da

[htrjdm]

帮顶...

[woshilitao5]

請教，如果是oracle 該怎么實現呢？

[fcuandy]

关系查询解法

DECLARE @s VARCHAR(1000)
SET @s='1,2,3,4,(5,6),7,(8,(9,10))'
DECLARE @x XML
SELECT @x= '<r>' + REPLACE(@s,',','</r><r>') + '</r>'
SELECT v = x.value('.','VARCHAR(100)'),id=ROW_NUMBER() OVER(ORDER BY GETDATE()) INTO #1 FROM @x.nodes('//r') AS t(x)


SELECT a.v v1,a.id id1,b.v v2,b.id id2
	INTO #2
	FROM #1 a
CROSS APPLY
	(
		SELECT TOP 1 * FROM #1 x
			WHERE x.id>=a.id
				AND
				((
				(SELECT SUM( LEN(REPLACE(c.v,')',''))-LEN(REPLACE(c.v,'(',''))) FROM #1 c WHERE c.id<=a.id)
				=
				
				LEN(x.v) - LEN(REPLACE(x.v,')','')) -
				ISNULL((
				SELECT SUM(LEN(d.v)-LEN(REPLACE(d.v,'(','')) ) FROM #1 d WHERE d.id>a.id  AND d.id<=x.id
				),0)
				)
				or (SELECT SUM( LEN(REPLACE(e.v,')',''))-LEN(REPLACE(e.v,'(',''))) FROM #1 e WHERE e.id<=a.id)=0
				)
			ORDER BY x.id

	) b
	ORDER BY a.id


SELECT MIN(id1) ids,MAX(id2) ide,id2 gid,IDENTITY(INT) NID INTO #3 FROM #2 GROUP BY id2

SELECT NID as gid,v=STUFF(v.value('/R[1]','nvarchar(max)'),1,1,'') FROM #3 a
CROSS APPLY
	(
		SELECT v=(SELECT N','+ v FROM #1 WHERE id BETWEEN ids AND ide FOR XML PATH(''), ROOT('R'), TYPE)
	) b



/*
1	1
2	2
3	3
4	4
5	(5,6)
6	7
7	(8,(9,10))
*/
DROP TABLE #1,#2,#3

[fcuandy]

好像有点点问题哈,算(时有点不对.

[dinoalex]

一定要用SQL语句来拆吗?

如果不用的话,我之前有写过这样的一段程序可以解决.

[miranda88]

我是一家美资猎头公司的HR，现在需要招聘一位精通技术研发职位的猎头顾问，如果各位精英认识这样的猎头，请与我联系，谢谢！
联系方式：msn：[e-mail]

[iefcu]

只是顶一下

[冰岛男孩]

学习了

[xiedi1209]

各位大侠真的是给了我们很多帮助 不是只在这一题上 而是给了我们以后工作和学习遇到问题的一个思路 感谢

[qgylovelj]

jf

[ken2002]

收藏了

[LQjianganyifengyu]

好强啊，领教了，虚心学习！

[sashimi1982]

恩

[TalesTen]

顶一个~~

[milk111]

顶下

[demonxp]

取经的.....

[lovezx1028]

up

[lemonjiamm]

学习了，正好最近在研究这个 呵呵

[blueberrySunny]

呵呵，我这个是最笨的方法啦，不知行不行：
declare @str nvarchar(max);
declare @count int;
declare @pointer int;
declare @before int;
declare @beforeT table (B int);
declare @Result table (Result nvarchar(50));
declare @flag bit;
declare @Comma int;
set @str = '19,2,3,(4,5),((6,7),8),9,(((10,11),12),13),(14,(15,16)),37,(18,(19,(20,21)))';
set @count = 0;
set @pointer = 1;
set @before = 0;
set @flag = 0;
set @Comma = 0;
while (@pointer <= LEN(@str))
begin
if(SUBSTRING(@str,@pointer,1)= '(')
begin
set @count = @count + 1;
end

else if (SUBSTRING(@str,@pointer,1)= ')')
begin
set @count = @count - 1;
set @flag = 1;
end

if (@count = 1 and SUBSTRING(@str,@pointer,1)= '(')
begin
insert into @beforeT values(@pointer);
end

if (@count = 0 and SUBSTRING(@str,@pointer,1) != ',' and @flag = 1 )
begin
set @before = (select top 1 B from @beforeT order by B desc );
insert into @Result values (SUBSTRING(@str,@before,@pointer-@before+1))
end

if (@count = 0 and (SUBSTRING(@str,@pointer,1) != ',' ) and @flag = 0)
begin
set @Comma = @pointer;
while (substring(@str,@Comma,1) != ',')
begin
set @Comma = @Comma + 1
end

insert into @Result values (SUBSTRING(@str,@pointer,@Comma-@pointer))
end

set @pointer = @pointer + 1;
set @flag = 0;
end
select * from @Result

输出结果：
--------------------------
19
9
2
3
(4,5)
((6,7),8)
9
(((10,11),12),13)
(14,(15,16))
37
7
(18,(19,(20,21)))