查询字符在字符串中第几次出现的位置

 SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--select   txmsg ,charindex('|',txmsg)   tillid,docno,txtype,txdate,postdate,txtime  from trans  where tillid='20'  and txdate='2007-06-18' and txtype='4'  order by docno,txtype

-- select len(replace(txmsg,'|','||'))-len(txmsg) from  trans  where tillid='20'  and txdate='2007-06-18' and txtype='4'  order by docno,txtype

ALTER   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)

 /*
 select   txmsg,
 txmsg,
substring(txmsg,
dbo.StrSite(txmsg,'|',3)+1,
dbo.StrSite(txmsg,'|',4)-
 dbo.StrSite(txmsg,'|',3)-1)  as je
  from trans where tillid='20'  and txdate='2007-06-18' and txtype='4'  order by docno,txtype
*/
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO