我在一个表上创建了一个全文索引列。
我有一个存储过程,我可以将变量"search this text"的值传递给它。我想在全文列中搜索"search"、"this"one_answers"text"。要搜索的字数是可变的。
我可以用之类的东西
WHERE column LIKE '%search%' OR column LIST '%this%' OR column LIKE '%text%'
但这需要我使用动态SQL,而我正试图避免这种情况。
我如何使用全文搜索来查找每个单词,大概是使用CONTAINS,而不将整个存储过程转换为动态SQL?
如果你说你肯定启用了SQL表全文搜索,那么你可以使用下面的查询。
select * from table where contains(columnname,'"text1" or "text2" or "text3"' )
有关的详细信息,请参阅下面的链接
全文索引工作台
供您考虑。
我理解你的前辈想要避免动态SQL,但我坚信动态SQL不是邪恶的。
在下面的例子中,您可以看到,通过一些参数(甚至默认值)和3行代码,您可以:
1) Dynamically search any source
2) Return desired or all elements
3) Rank the Hit rate
SQL
Declare @SearchFor varchar(max) ='Daily,Production,default' -- any comma delim string
Declare @SearchFrom varchar(150) ='OD' -- table or even a join statment
Declare @SearchExpr varchar(150) ='[OD-Title]+[OD-Class]' -- Any field or even expression
Declare @ReturnCols varchar(150) ='[OD-Nr],[OD-Title]' -- Any field(s) even with alias
Set @SearchFor = 'Sign(CharIndex('''+Replace(Replace(Replace(@SearchFor,' , ',','),', ',''),',',''','+@SearchExpr+'))+Sign(CharIndex(''')+''','+@SearchExpr+'))'
Declare @SQL varchar(Max) = 'Select * from (Select Distinct'+@ReturnCols+',Hits='+@SearchFor+' From '+@SearchFrom + ') A Where Hits>0 Order by Hits Desc'
Exec(@SQL)
返回
OD-Nr OD-Title Hits
3 Daily Production Summary 2
6 Default Settings 1
我应该补充一点,我的搜索字符串是逗号分隔的,但您可以更改为空格。
另一个注意事项是CharIndex可以比LIKE快得多。看一眼http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex
所以我想我想出了一个解决方案。我创建了以下标量函数:
CREATE FUNCTION [dbo].[fn_Util_CONTAINS_SearchString]
(
@searchString NVARCHAR(MAX),
@delimiter NVARCHAR(1) = ' ',
@ANDOR NVARCHAR(3) = 'AND'
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
IF @searchString IS NULL OR LTRIM(RTRIM(@searchString)) = '' RETURN NULL
-- trim leading/trailing spaces
SET @searchString = LTRIM(RTRIM(@searchString))
-- remove double spaces (prevents empty search terms)
WHILE CHARINDEX(' ', @searchString) > 0
BEGIN
SET @searchString = REPLACE(@searchString,' ',' ')
END
-- reformat
SET @searchString = REPLACE(@searchString,' ','" ' + @ANDOR + ' "') -- replace spaces with " AND " (quote) AND (quote)
SET @searchString = ' "' + @searchString + '" ' -- surround string with quotes
RETURN @searchString
END
我可以得到我的结果:
DECLARE @ftName NVARCHAR (1024) = dbo.fn_Util_CONTAINS_SearchString('value1 value2',default,default)
SELECT * FROM Table WHERE CONTAINS(name,@ftName)
如有任何意见/建议,我将不胜感激。