select (len(name)- len(replace(name,'aa',''))/2 from tbl
/*按照符号分割字符串*/ create function [dbo].[m_split](@c varchar(2000),@split varchar(2)) returns @t table(col varchar(200)) as begin while(charindex(@split,@c)<>0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end insert @t(col) values (@c) return end select * from dbo.m_split('aa,aa,aa,cc,bb,dd,aa',',') /* col -------- aa aa aa cc bb dd aa */ -- 求aa的个数 select count(1) from dbo.m_split('aa,aa,aa,cc,bb,dd,aa',',') where col='aa' /* 4 */
declare @str varchar(100) set @str='aa,aa,aa,cc,bb,dd,aa'select (len(','+@str+',')- len(replace(','+@str+',',',aa,','')))/4 ----------- 3(1 行受影响)
declare @str varchar(100) set @str='aa,aa,aa,cc,bb,dd,aa'select count(1) as gs from master..spt_values as m where m.type='P' and SUBSTRING(','+@str+',',m.number,4)=',aa,';gs ----------- 4(1 行受影响)
select (len(name)- len(replace(name,'aa',''))/2 from tbl
/*按照符号分割字符串*/
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
select * from dbo.m_split('aa,aa,aa,cc,bb,dd,aa',',')
/*
col
--------
aa
aa
aa
cc
bb
dd
aa
*/
-- 求aa的个数
select count(1) from dbo.m_split('aa,aa,aa,cc,bb,dd,aa',',') where col='aa'
/*
4
*/
set @str='aa,aa,aa,cc,bb,dd,aa'select (len(','+@str+',')- len(replace(','+@str+',',',aa,','')))/4
-----------
3(1 行受影响)
set @str='aa,aa,aa,cc,bb,dd,aa'select count(1) as gs
from master..spt_values as m
where m.type='P' and SUBSTRING(','+@str+',',m.number,4)=',aa,';gs
-----------
4(1 行受影响)