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