sql表中有一分号列，如何统计这列中没行分号的个数
sql表中有一分号列，如何统计这列中没行分号的个数
如:
1 a;b;c
2 a;b
3 cc;dd;33
查询结果
名字 个数
1 3
2 2
3 3
[解决办法]

;with cte(id,value) as
(
select 1,'a;b;c'
union all select 2,'a;b'
union all select 3,'cc;dd;33'
)
select id,LEN(value)-LEN(replace(value,';',''))+1 as result
from cte

/*
idresult
13
22
33
*/

;with cte(id,value) as
(
select 1,'a;b;c'
union all select 2,'a;b'
union all select 3,'cc;dd;33'
union all select 4,'xx'
union all select 5,''
)
select id,LEN(value)-LEN(replace(value,';',''))+case when LEN(value)>0 then 1 else 0 end as result
from cte

/*
idresult
13
22
33
41
50
*/

declare @t table(id int ,value varchar(100)) 

insert into @t
select 1,'a;b;c'
union all select 2,'a;b'
union all select 3,'cc;dd;33'
union all select 4,''


select id as 名字,
       len(value) - len(replace(value,';','')) as 个数
from @t t
/*
名字个数
12
21
32
40
*/

create table wg
(x int,y varchar(10))

insert into wg
 select 1,'a;b;c' union all
 select 2,'a;b' union all
 select 3,'cc;dd;33'


select a.x '名字',
       count(substring(a.y,b.number,charindex(';',a.y+';',b.number)-b.number)) '个数'
 from wg a
 inner join master.dbo.spt_values b
 on b.type='P' and b.number between 1 and len(a.y)
 and substring(';'+a.y,b.number,1)=';'
 group by a.x

/*
名字          个数
----------- -----------
1           3
2           2
3           3

(3 row(s) affected)
*/