mssql 查询 字符在字段中出现次数的方法和代码
mssql 查询 字符出现次数
如何返回一个字段中，某个字符出现的次数。用SQL语句实现select len(字段) - len(replace(字段,'字符','')) as 字符出现次数 from 你的表
declare @test nvarchar(4000)
select @test = fieldname from yourtable
select len(@test) - len(replace(@test,test_char,'')) as times
select len(replace(字段,'字符','字符x'))-len(字段) as 字符出现次数 from 你的表
-------------------------------mssql在一个字符串里寻找某个字符出现的次数和在字符串里的相应位置
/*exec angel '"','"1", "100001", "dsdsds", "dsfs", "dsdsff", "3190051", "20030421-20030520", "0304008001", "27.00", "0.00", "0.00", "0.00", "27.00", "0.00", "0.00", "0.00", "0.00", "0.00", "5.40", "21.60", "0.00", "0.00", "0.00", "0.00", "21.60"'*/
--drop proc angel
create proc angel
(@variable varchar(300), --要寻找的字符
@char varchar(800)) --目标字符串
as
begin
declare @abc varchar(800)
set @abc= @char
create table #zzx(zzx varchar(300))
insert #zzx
select @abc
create table #angel(a int,b int)
declare @a varchar(300),@b varchar(300),@m int,@n int
select @m=0
while (select charindex(@variable,zzx,1) from #zzx)<>0
begin
select @a=ltrim(rtrim(zzx)) from #zzx
select @m=@m+1
select @n=charindex(@variable,@a,1)
update #zzx set zzx=stuff(@a,(charindex(@variable,@a,1)),1,'0')
insert #angel(a,b)
select @m,@n --@m为出现的次数，@n为位置，最大值的@m为次数的总数
end
select * from #angel
drop table #zzx
drop table #angel
end
