Find rows containing all keywords within a string across multiple columns I'm trying to create a universal search functionality. The goal is to compare the values of multiple columns and see if all of the provided keywords appear in those columns. For various reasons, using a Full Text Index and CONTAINS is not an option, nor is utilizing a CLR function. Are there any other options to solve this problem?

Example:

----------------------------------------------------
|    Row    |    Col1    |    Col2    |    Col3    |
----------------------------------------------------
|     1     |    Foo     |    Bar     |    Stack   |
----------------------------------------------------
|     2     |    Foo     |    Blitz   |    Stack   |
----------------------------------------------------
|     3     |    Foo     |     Foo    |     Bar    |
----------------------------------------------------


Search text "Foo Bar" matches Rows 1 and 3.

Search text "Foo Stack" matches Rows 1, and 2.

Search Text "Foo" matches Rows 1, 2 and 3.

Search text "Foo Bar Stack Blitz" matches no rows.

Search text "Foo Blitz" matches Row 2.
You can use a string splitter and do this. I use Jeff Moden's splitter but you can use your favorite. This essentially splits the string on the space, and then does a FULL JOIN and only returns rows where there is a mach for each word. This is handy since it doesn't care the order of the words in the string. i.e. Foo Bar is the same as Bar Foo. 

SQL FIDDLE

declare @table table ([Row] int identity (1,1), Col1 char(3), Col2 varchar(6), Col3 varchar(6))
insert into @table
values

('Foo','Bar','Stack'),
('Foo','Blitz','Stack'),
('Foo','Foo','Bar')


declare @search varchar(64)

set @search = 'Foo Bar'

select distinct
    t.*
from @table t
full join 
    dbo.DelimitedSplit8K(@search,' ') s on
    s.Item = t.Col1
    or s.Item = t.Col2
    or s.Item = t.Col3
where
    t.Row is not null
group by
    t.Row
    ,t.Col1
    ,t.Col2
    ,t.Col3
having
    count(*) = (select max(ItemNumber) from dbo.DelimitedSplit8K(@search,' '))