求数量的问题

[helinhai]

dianhao   A        B        C        D         E       F
10211034	NULL	NULL	NULL	NULL	3;5;6;7	NULL
10211529	2;10	10	2;10	2;10	NULL	NULL
10211536	5;6;8	NULL	5;6;8	6;9;10	NULL	NULL



需要的数据
dianhao   A        B        C        D         E       F
10211034	 0	0	0	0	4	0
10211529	 2	1	2	2	0	0
10211536	 3	0	3	3	0	0


也就是根据"；"号来区分有多少个

[fpzgm]

select dianhao,
       len(replace(A,';',';;'))-len(A),
       len(replace(B,';',';;'))-len(B),
       len(replace(C,';',';;'))-len(C),
       len(replace(D,';',';;'))-len(D),
       len(replace(E,';',';;'))-len(E),
       len(replace(F,';',';;'))-len(F)
from [table]

[黄_瓜]

--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([dianhao] int,[A] varchar(5),[B] int,[C] varchar(5),[D] varchar(6),[E] varchar(7),[F] varchar(7))
insert #tb
select 10211034,null,null,null,null,'3;5;6;7',null union all
select 10211529,'2;10',10,'2;10','2;10',null,null union all
select 10211536,'5;6;8',null,'5;6;8','6;9;10',null,null
go

-->测试开始
select [dianhao],len([A])-len(replace([A],';','')) as [A],
len([B])-len(replace([B],';','')) as [B],
len([C])-len(replace([C],';','')) as [C],
len([D])-len(replace([D],';','')) as [D],
len([E])-len(replace([E],';','')) as [E],
len([F])-len(replace([F],';','')) as [F]
from #tb
/*
dianhao     A           B           C           D           E           F
----------- ----------- ----------- ----------- ----------- ----------- -----------
10211034    NULL        NULL        NULL        NULL        3           NULL
10211529    1           0           1           1           NULL        NULL
10211536    2           NULL        2           2           NULL        NULL

(3 行受影响)


*/

[黄_瓜]

[Quote=引用 2 楼 beirut 的回复:]
SQL code
--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([dianhao] int,[A] varchar(5),[B] int,[C] varchar(5),[D] varchar(6),[E] varchar(7),[F] varchar(7)……
[/Quote]
都少了一个，哈哈 就这样了 自己改改吧

[闹铃]

select dianhao,
       len(A)-len(replace(A,';','')),
       len(B)-len(replace(B,';','')),
       len(C)-len(replace(C,';','')),
       len(D)-len(replace(D,';','')),
       len(E)-len(replace(E,';','')),
       len(F)-len(replace(F,';',''))
from [table]  

[fpzgm]

--更正
--要分情况，可以在原来上面加case when，有的要加1
--或者建个函数
create function dbo.str(@str varchar)
return int
as
begin
declare @result int
select @result=case when @str is null then 0
                    when @str is not null and charindex(';',@str)=0 then 1
                    else len(replace(@str,';',';;'))-len(@str)+1
               end
return @result
end

--调用函数
select dianhao,dbo.str(A),dbo.str(B),dbo.str(C),dbo.str(D),dbo.str(E),dbo.str(F)
from [table]
       

[fpzgm]

--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([dianhao] int,[A] varchar(5),[B] int,[C] varchar(5),[D] varchar(6),[E] varchar(7),[F] varchar(7))
insert #tb
select 10211034,null,null,null,null,'3;5;6;7',null union all
select 10211529,'2;10',10,'2;10','2;10',null,null union all
select 10211536,'5;6;8',null,'5;6;8','6;9;10',null,null
go

create function dbo.str(@str varchar(10))
returns int
as
begin
declare @result int
select @result=case when @str is null then 0
                    when @str is not null and charindex(';',@str)=0 then 1
                    when charindex(';',@str)>1 then  len(replace(@str,';',';;'))-len(@str)+1
               end
return @result
end



select dianhao,dbo.str(A) A,dbo.str(B) B,dbo.str(C) C
      ,dbo.str(D) D,dbo.str(E) E,dbo.str(F) F
from #tb

/*

dianhao   A    B    C    D    E    F
10211034  0    0    0    0    4    0
10211529  2    1    2    2    0    0
10211536  3    0    3    3    0    0
*/

[dawugui]

create table tb([dianhao] int,[A] varchar(5),[B] int,[C] varchar(5),[D] varchar(6),[E] varchar(7),[F] varchar(7))
insert tb
select 10211034,null,null,null,null,'3;5;6;7',null union all
select 10211529,'2;10',10,'2;10','2;10',null,null union all
select 10211536,'5;6;8',null,'5;6;8','6;9;10',null,null

select dianhao,
       case when len(a) - len(replace(a,';','')) is null then 0 else len(a) - len(replace(a,';','')) + 1 end A,
       case when len(b) - len(replace(b,';','')) is null then 0 else len(b) - len(replace(b,';','')) + 1 end B,
       case when len(c) - len(replace(c,';','')) is null then 0 else len(c) - len(replace(c,';','')) + 1 end C,
       case when len(d) - len(replace(d,';','')) is null then 0 else len(d) - len(replace(d,';','')) + 1 end D,
       case when len(e) - len(replace(e,';','')) is null then 0 else len(e) - len(replace(e,';','')) + 1 end E,
       case when len(f) - len(replace(f,';','')) is null then 0 else len(f) - len(replace(f,';','')) + 1 end F
from tb

drop table tb

/*
dianhao     A           B           C           D           E           F           
----------- ----------- ----------- ----------- ----------- ----------- ----------- 
10211034    0           0           0           0           4           0
10211529    2           1           2           2           0           0
10211536    3           0           3           3           0           0

（所影响的行数为 3 行）

*/

[hello926]

Create table dot(
dianhao varchar(10),
A VARCHAR(10),
B VARCHAR(10),
C VARCHAR(10),
D VARCHAR(10),
E VARCHAR(10),
F VARCHAR(10)
)
go
insert into dot
select '10211034' , NULL , NULL , NULL ,NULL , '3;5;6;7' , NULL union all
select '10211529' , '2;10 ' , '10', '2;10', '2;10 ', NULL , NULL union all
select '10211536' , '5;6;8', NULL , '5;6;8' , '6;9;10' , NULL , NULL

查询语句

select dianhao,
CASE WHEN A IS NULL THEN 0 ELSE ISNULL(len(A)-LEN(replace(A,';','')),0)+1 END AS 'A',
CASE WHEN B IS NULL THEN 0 ELSE ISNULL(len(B)-LEN(replace(B,';','')),0)+1 END AS 'B',
CASE WHEN C IS NULL THEN 0 ELSE ISNULL( len(C)-LEN(replace(C,';','')),0)+1 END AS 'C',
CASE WHEN D IS NULL THEN 0 ELSE ISNULL( len(D)-LEN(replace(D,';','')),0)+1 END AS 'D',
CASE WHEN E IS NULL THEN 0 ELSE ISNULL( len(E)-LEN(replace(E,';','')),0)+1 END AS 'E',
CASE WHEN F IS NULL THEN 0 ELSE ISNULL( len(F)-LEN(replace(F,';','')),0)+1 END AS 'F'
FROM dot

[chenxippt]

select isnull((len('2;3;4')-len(replace('2;3;4',';','')))+1,0)