动态调整存储过程，一个变量包含多个值

I have a stored procedure that takes an input parameter as follows:

我有一个存储过程，它接受如下输入参数:

@Name NVARCHAR(50) = 'George W Bush'

How can I adjust my where clause dynamically:

如何动态调整where子句:

SELECT * FROM TABLE
WHERE CONTAINS(FIELD, 'George')
  AND CONTAINS(FIELD, 'W')
  AND CONTAINS(FIELD, 'Bush')

Or for example:

例如:

@Name NVARCHAR(50) = 'Harry Potter'

SELECT * FROM TABLE
WHERE CONTAINS(FIELD, 'Harry')
  AND CONTAINS(FIELD, 'Potter')

I have found many solutions here that come so close to doing what I need, but I specifically need for the field to contain each substring of the input parameter.

我在这里找到了许多解决方案，它们非常接近于我所需要的，但是我特别需要字段包含输入参数的每个子字符串。

4 个解决方案

#1

CONTAINS can work with multiple words with AND or OR conditions like this:

包含可以与多个单词一起使用，或类似的条件:

 WHERE CONTAINS (FIELD, 'George AND W AND Bush')

So what you can do is change your variable to(given they will always be space-delimited):

所以你能做的就是把你的变量改为(因为它们总是空格分隔的):

DECLARE @Name NVARCHAR(50)
SET @Name = REPLACE('George W Bush', ' ', ' AND ')

SELECT * FROM TABLE
WHERE CONTAINS(FIELD, @Name)

#2

Here is a way to split your names, regardless if it is FirstName LastName, or FirstName MiddleName LastName, into parameters which you can use. Of course if you expect more than 2 spaces (three names) this won't work

这里有一种方法，可以将您的名字分割成可以使用的参数，不管它是FirstName LastName，还是FirstName MiddleName LastName。当然，如果您期望超过2个空格(3个名字)，这是行不通的

DECLARE @Name NVARCHAR(50) = 'George W Bush' 
DECLARE @Spaces int = (SELECT LEN(@Name)-LEN(REPLACE(@Name, ' ', '')))

DECLARE @FirstName varchar(50) = null
DECLARE @MiddleName varchar(50) = null
DECLARE @LastName varchar(50) = null

IF @Spaces = 2 
    BEGIN
        SET @FirstName = LEFT(@Name,CHARINDEX(' ',@Name))
        SET @MiddleName = SUBSTRING(@Name,CHARINDEX(' ',@Name) + 1,1)
        SET @LastName = RIGHT(@Name,CHARINDEX(' ',REVERSE(@Name)))
    END
ELSE IF @Spaces = 1 
    BEGIN
        SET @FirstName = LEFT(@Name,CHARINDEX(' ',@Name))
        SET @LastName = RIGHT(@Name,CHARINDEX(' ',REVERSE(@Name)))
    END


SELECT @FirstName, @MiddleName, @LastName

#3

CONTAINS will work if your table is full-text indexed, but if it isn't, consider this:

如果您的表是全文索引的，则包含将起作用，但如果不是，请考虑以下内容:

-- Here's my test data
declare @t table
(
   text nvarchar(max)
)

insert @t values('I''m just wild about Harry')
              , ('Sally Potter is important in British theater')
              , ('I love the "Harry Potter" books')


-- Here are our keywords:
declare @Name varchar(max) = 'Harry Potter'

-- To make this work, we need to convert the substrings into tabular form.  
-- so here's how we'll do that. 

declare @NameXml xml 

set @NameXml = convert(xml, '<substrings><substring>' + 
               replace(@Name, ' ', '</substring><substring>') +
               '</substring></substrings>')

-- That gives us an XML variable with each substring as a separate 
-- element.
;
with mockTable as 
(
    select  Keyword = N.a.value('.[1]', 'varchar(100)')
    from    @NameXml.nodes('/substrings/substring') as N(a)
)
-- Now that we have a "mock table", we can use the ALL operator to 
-- confirm that each string in our mock table is found in the
-- records we return.
select  *
from    @t
where   1 = ALL (
    select  case 
            when [text] like '%' + Keyword + '%'
            then 1
            else 0
            end
    from mockTable
    )

#4

Like Ann L. said, CONTAINS and FREETEXT only work when you've already created the FULLTEXT INDEX on the underlying table. As per you need this string-parsing operation, why not create a user-defined function to handle that, and use it within your Sproc. BTW, I really dont want to use wild cards. :)

如Ann L. said, CONTAINS和FREETEXT仅在您已经在底层表上创建了FULLTEXT索引时才会工作。正如您需要这个字符串解析操作一样，为什么不创建一个用户定义的函数来处理它，并在Sproc中使用它呢?顺便说一句，我真的不想用外卡。:)

CREATE FUNCTION [dbo].[parsing]
(
    @String NVARCHAR(2000),
    @Delimiter NVARCHAR(5)
)
RETURNS @Rtn TABLE
(
    ID INT IDENTITY(1,1),
    Seg NVARCHAR(50)
)
AS
BEGIN
    WHILE(CHARINDEX(@Delimiter,@String)>0)
    BEGIN
        INSERT INTO @Rtn (Seg)
        SELECT LTRIM(RTRIM(SUBSTRING(@String,1,CHARINDEX(@Delimiter,@String)-1)))
        SET @String = SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+LEN(@Delimiter),LEN(@String))
    END
    INSERT INTO @Rtn (Seg)
    SELECT LTRIM(RTRIM(@String))
END

#1