22,301
社区成员




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
也就是根据";"号来区分有多少个
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 行)
*/
--> 测试数据:#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
*/
--更正
--要分情况,可以在原来上面加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]
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]
--> 测试数据:#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 行受影响)
*/
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]