SQL Server 2008多个“LIKE”问题 [英] SQL server 2008 multiple "LIKE" problem

问题描述

我使用的是MS SQL 2008，现在我面临着几天的挑战。
我的SP参数可以在一个字符串（nvarchar）中包含一到三个单词，并且我必须为字符串中的每个单词返回匹配的 LIKE％短语％记录。

示例。我的参数是：

pre $ code>stack overflow

必须记录的记录：

  miSTACKon 
 noOVERFLOWon 
 STACKit 
 poOWERFLOW 
 STACK 
 OWERFLOW 
  

我也考虑过FTS但CONTAINS函数在（每个）短语的末尾只有一个通配符。

 短语* 
  

除了动态SQL之外，还有解决这个问题的方法吗？

解决方案

从一般示例开始，然后我会用一些union alls来模拟它。 b

 从dbo.Records 
中选择distinct记录inner join dbo.Split（'stack overflow'，''）令牌在records_table上记录
。记录如'％'+ tokens.value +'％'
  

一些数据是记录，以及来自dbo.Split函数的模拟返回，基本上是一个在''上标记为'stack'和'overflow'的varchars表。

 从（
）选择不同名称名称
联合所有
选择'nope'作为名称
 union all 
选择'stackoverflow'作为名称
）records_table 
内部连接（
选择'stack'作为值
 union all 
选择'overflow'作为值）像'％'+ tokens.value +'％'在records_table.Name上记录
 '
  

结果：

  stack 
 stackoverflow 
  

没有什么特别的dbo.Split函数，并且在那里有大量的实现示例...

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.

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

phrase*

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

解决方案

Start with the generic example and then i will mock it up with some "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 ' ' .

select distinct Name from (
 select 'stack' as Name
 union all
 select 'nope' as Name
 union all
 select ' stackoverflow' 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
stackoverflow

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

这篇关于SQL Server 2008多个“LIKE”问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！