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)
*/
