CREATE procedure sp_findsubstring @ValueString varchar(8000), @lookupvalue varchar(25) as -- Procedure Name: sp_findsubstring -- Creation Date: 11/16/2000 declare @position1 int, @position2 int, @Lookup varchar(25), @Firstchar char(3), @found int, @cnt int, @len1 int, @len2 int select @found = 0 select @cnt = 0 select @lookupvalue = Ltrim(@lookupvalue) select @lookupvalue = Rtrim(@lookupvalue) select @position1 = 1 select @position2 = 0-- Get substring length Select @len1 = len(@lookupvalue) -- find first char and place '%' on each side of it Select @Firstchar = '%' + substring(@lookupvalue, 1, 1) + '%' select @position2 = patindex(@Firstchar, substring(@ValueString,@position1,8000)) While (@position2 <> 0 ) Begin Select @lookup = substring(@ValueString,@position2,@len1 ) If @Lookup = @lookupValue Begin select @found = @found + 1 End -- Augment the search positions If @len1 = 1 Select @position1 = @position2 + @len1 Else Select @position1 = @position2 + @len1 - 1 Select @position2 = patindex(@Firstchar, substring(@ValueString,@position1,8000)) -- If we find the first character again we need to set -- the positions to get the next # of chars to check If( @position2 <> 0) Begin select @position2 = (@position2 - 1 ) + @position1 End End print 'The string has ' + convert(varchar(10),@found) + ' occurrences of the substring ''' + @lookupvalue + '''' go调用: sp_findsubstring 'kdfaasd aa lkfjaaksl;d','aa'
print 'in'
expression1一个表达式,其中包含要寻找的字符的次序。expression1 是一个短字符数据类型分类的表达式。expression2一个表达式,通常是一个用于搜索指定序列的列。expression2 属于字符串数据类型分类。start_location在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定 start_location,而是一个负数或零,则将从 expression2 的起始位置开始搜索。返回类型
@ValueString varchar(8000),
@lookupvalue varchar(25)
as
-- Procedure Name: sp_findsubstring
-- Creation Date: 11/16/2000
declare
@position1 int,
@position2 int,
@Lookup varchar(25),
@Firstchar char(3),
@found int,
@cnt int,
@len1 int,
@len2 int
select @found = 0
select @cnt = 0
select @lookupvalue = Ltrim(@lookupvalue)
select @lookupvalue = Rtrim(@lookupvalue)
select @position1 = 1
select @position2 = 0-- Get substring length
Select @len1 = len(@lookupvalue)
-- find first char and place '%' on each side of it
Select @Firstchar = '%' + substring(@lookupvalue, 1, 1) + '%'
select @position2 = patindex(@Firstchar,
substring(@ValueString,@position1,8000))
While (@position2 <> 0 )
Begin
Select @lookup = substring(@ValueString,@position2,@len1 )
If @Lookup = @lookupValue
Begin
select @found = @found + 1
End
-- Augment the search positions
If @len1 = 1
Select @position1 = @position2 + @len1
Else
Select @position1 = @position2 + @len1 - 1
Select @position2 = patindex(@Firstchar,
substring(@ValueString,@position1,8000))
-- If we find the first character again we need to set
-- the positions to get the next # of chars to check
If( @position2 <> 0)
Begin
select @position2 = (@position2 - 1 ) + @position1
End
End
print 'The string has ' + convert(varchar(10),@found) + '
occurrences of the substring ''' + @lookupvalue + ''''
go调用:
sp_findsubstring 'kdfaasd aa lkfjaaksl;d','aa'