mssql拆分搜索时用到的关键词

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

ALTER function [mosi].[GetKeyword](@str nvarchar(max),@spliter nvarchar(10))
returns nvarchar(256)
AS

BEGIN
DECLARE @Num int,@Pos int, @NextPos int ,@strKeyword nvarchar(300)
SET @Num = 0
SET @Pos = 1
SET @strKeyword=' and ('
WHILE(@Pos <= LEN(@str))
BEGIN
 SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)
 IF (@NextPos = 0 OR @NextPos IS NULL)
  SELECT @NextPos = LEN(@str) + 1
 Declare @key nvarchar(20)
 set @key = RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos)));
 if @Pos = 1
 BEGIN
   set @strKeyword=@strKeyword+'(Keyword LIKE  ''%' +@key + '%'')';
 END
 set @strKeyword=@strKeyword+' or (Keyword LIKE  ''%' +@key + '%'')';
 SELECT @Pos = @NextPos+1
END
SET @strKeyword = @strKeyword+')'
return @strKeyword;
END

 

转载于:https://www.cnblogs.com/gaofeng-soft/archive/2011/03/08/1976834.html