declare @ErrorMesage varchar(70) set @ErrorMesage = 'a#b#c-d#' select charindex('#',@errormesage,charindex('#',@errormesage,charindex('#',@errormesage)+1)+1)
都不对呀,我说的是第3个#号出现的位置 例如declare @ErrorMesage varchar(70) set @ErrorMesage = 'a#b#c-d#qq#45!#'应该是8,还有有别的方法呢
/****函数说明:查询字符在字符串中第几次出现的位置 *******/create Function StrSite(@Str varchar(2000) ,@Word varchar(20) ,@TimesNo int) returns int as begin declare @i int,@Times int declare @WordLen int set @Times=0 set @i=1 set @Wordlen=len(@Word)
while (@i< Len(@Str)+1 ) begin if substring(@Str,@i,@Wordlen)=@word set @times=@times+1 --print '第'+cast(@i as varchar(20)) +'是 : ' +cast(@Times as varchar(20))if @times=@TimesNo break set @i=@i+1 end return (@i) end
go declare @ErrorMesage varchar(70) set @ErrorMesage = 'a#b#c-d#' select dbo.StrSite(@ErrorMesage,'#',3) /* --- 8 */drop function dbo.StrSite
上面的都不对,也不看看人家的需求 declare @ErrorMesage varchar(70) ,@cnt int,@ErrorMesage_old varchar(70) select @cnt=3--可根据实际情况,来改变其值,你这里就是10 set @ErrorMesage = 'a#b#c-d#...' select @ErrorMesage_old=@ErrorMesage while @cnt>0 begin set @ErrorMesage=substring(@ErrorMesage,charindex('#',@ErrorMesage)+1,len(@ErrorMesage)) set @cnt=@cnt-1 end select @cnt=len(@ErrorMesage_old)-len(@ErrorMesage)最后@cnt就是你要的值 --print isnull @ErrorMesage --print @cnt
set @ErrorMesage = 'a#b#c-d#'
select charindex('#',reverse(@ErrorMesage))/*
---
1
*/
set @ErrorMesage = 'a#b#c-d#'
select len(@ErrorMesage)-charindex('#',reverse(@ErrorMesage))+1 /*
---
8*/
declare @ErrorMesage varchar(70)
set @ErrorMesage = 'a#b#c-d#' select charindex('#',@errormesage,charindex('#',@errormesage,charindex('#',@errormesage)+1)+1)
例如declare @ErrorMesage varchar(70)
set @ErrorMesage = 'a#b#c-d#qq#45!#'应该是8,还有有别的方法呢
returns int
as
begin
declare @i int,@Times int
declare @WordLen int
set @Times=0
set @i=1
set @Wordlen=len(@Word)
while (@i< Len(@Str)+1 )
begin
if substring(@Str,@i,@Wordlen)=@word
set @times=@times+1
--print '第'+cast(@i as varchar(20)) +'是 : ' +cast(@Times as varchar(20))if @times=@TimesNo
break
set @i=@i+1
end
return (@i)
end
go
declare @ErrorMesage varchar(70)
set @ErrorMesage = 'a#b#c-d#'
select dbo.StrSite(@ErrorMesage,'#',3)
/*
---
8
*/drop function dbo.StrSite
set @ErrorMesage = 'a#b#c-d#'
select len(@ErrorMesage)-charindex('#',reverse(@ErrorMesage))+1 /*
---
8
*/如果是取第几个不确定的话,可以用我上面14楼的函数
declare @ErrorMesage varchar(70) ,@cnt int,@ErrorMesage_old varchar(70)
select @cnt=3--可根据实际情况,来改变其值,你这里就是10
set @ErrorMesage = 'a#b#c-d#...'
select @ErrorMesage_old=@ErrorMesage
while @cnt>0
begin
set @ErrorMesage=substring(@ErrorMesage,charindex('#',@ErrorMesage)+1,len(@ErrorMesage))
set @cnt=@cnt-1
end
select @cnt=len(@ErrorMesage_old)-len(@ErrorMesage)最后@cnt就是你要的值
--print isnull @ErrorMesage
--print @cnt