SQL server 2008 multiple "LIKE" problem

Question

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?

Answer 1

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...