查询字符在字符串中第几次出现的位置_sql查询字符在字符串第二次出现-优快云博客

本文介绍了一段SQL查询代码及一个自定义的T-SQL函数，该函数用于查找特定子串在字符串中首次出现的位置，并演示了如何使用此函数从交易记录中提取所需的数据片段。

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