SQL server 2008多个“LIKE”问题

I'm using MS SQL 2008 and I'm facing a challenge for a few day's now. My SP parameter can contain one to three words in a string (nvarchar) and I have to return matching LIKE %phrase% records for each word in a string.

我正在使用MS SQL 2008，我现在正面临着几天的挑战。我的SP参数可以在一个字符串(nvarchar)中包含一到三个单词，我必须为字符串中的每个单词返回类似%phrase%的记录。

Example. My parameter is:

的例子。我的参数是:

"stack overflow"

Records that must be returnd:

必须归还的记录:

miSTACKon
noOVERFLOWon
STACKit
poOWERFLOW
STACK
OWERFLOW

I also considered FTS but CONTAINS function takes only one wildcard at the end of the (each) phrase

我也考虑过FTS，但是CONTAINS function在(每个)短语末尾只使用一个通配符

phrase*

Is there a solution to this problem other than dynamic SQL?

除了动态SQL，还有其他解决方案吗?

2 个解决方案

#1

Start with the generic example and then i will mock it up with some "union alls"

从通用示例开始，然后我将用一些“union alls”对它进行模拟

   select distinct Record from dbo.Records
     inner join dbo.Split('stack overflow', ' ') tokens 
       on records_table.Record like '%' + tokens.value + '%'

So what I did below is i mocked some data that are the "records, as well as a mock return from the dbo.Split function, basically a table of varchars with 'stack' and 'overflow' tokenized on ' ' .

因此，我在下面所做的是嘲笑一些数据，它们是“记录，以及来自dbo的模拟返回”。分割函数，基本上是一个带有“堆栈”和“溢出”标记的varchars表。

select distinct Name from (
 select 'stack' as Name
 union all
 select 'nope' as Name
 union all
 select ' *' as Name
   ) records_table 
   inner join (
   select 'stack' as value
     union all
    select 'overflow' as value) tokens 
    on records_table.Name like '%' + tokens.value + '%'

Results:

结果:

stack
*

There is nothing special about the dbo.Split function and there are tons of implementation examples out there...

dbo没有什么特别之处。拆分函数和大量的实现示例…

#2

You could pass the 'words' with a fixed delimiter character (i.e. ',', '|', etc.) that isn't otherwise needed for any possible 'words' (that you're supporting), then parse that argument with a table-valued function, and finally join the set of words with a JOIN condition similar to LIKE '%' + word + '%'.

您可以传递带有固定分隔符的“words”(例如。'、'、'|'等)，这些都不是任何可能的'words'(您所支持的)所需要的，然后使用表值函数解析该参数，最后使用与'%' + word + '%'类似的连接条件加入一组单词。

#1