求拆分字符串的通用算法计划

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

我现在有一个字符串\"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个出来……

declare @s varchar(100)

set @s=\"1,2,3,4,(5,6),7,(8,(9,10))\"

select

(len(@s)-len(replace(@s,\",\",\"\")))

-(len(@s)-len(replace(@s,\"(\",\"\")))

/**

-----------

6

（所影响的行数为 1 行）

**/

SQL codecreate table #tb

(

id int,

col varchar(50),

num int

)

insert into #tb select 1,\"aa,bb,cc\",10

union all select 2,\"aa,aa,bb\",20

union all select 3,\"aa,aa,bb\",20

union all select 4,\"dd,ccc,c\",30

union all select 5,\"ddaa,ccc\",40

union all select 6,\"eee,ee,c\",50

declare @Len int

select top 1 @Len=len(col)+1 from #tb order by len(col)

select @Len

set rowcount @Len

董明珠说 select ID=identity(int,1,1) into #TT from scolumns A,scolumns B

set rowcount 0

;with hgo as

(

select ,

number=substring(col,,charindex(\",\",col+\",\",))

from #TT A join #tb b on substring(\",\"+col,,1)=\",\"

)

select number,count(distinct id) [count],count(number) [number] from hgo group by number