Full-text search per Sean's recommendation definitely sounds like the best long-term solution. But implementing full-text search is not trivial either. To be effective you will need to look up a good parser function for "Google-like" searches (for example) and then learn how to write queries using the proper look-up methods for the full-text index. That will require you make some decisions such as what rowset function to use (freetext vs contains for example).

In your case, since you are looking for exact discrete matches, Sean's other suggestion to use a better split method may be enough by itself to get what you want. Point by point:

1. Split the target string via CROSS APPLY by spaces into a temp table using a good inline table-value splitter function. This will give you a table with every word of the target in its own row.

2. Use DelimitedSplit8K to split your string of search words into another table (the function will do that for you).

3. Then CROSS APPLY the first table with the second to filter your results.

pseudo-code:

SELECT

s.strInput --string being searched

,dsk1.ItemValue --terms in the search term CSV that match SplitOnSpace temp table

FROM

SourceTable s

CROSS APPLY

SplitOnSpace(s.strInput) spl1

CROSS APPLY

DelimitedSplit8K(s.SearchTermCSV,',') dsk1

WHERE

spl1.strValue = dsk1.ItemValue

strInput = the text you are parsing. You would get a temp table with (for example) an output column strValue with each word of the target string now parsed into its own row.

SearchTermCSV = this is a delimited list of search terms. It gets parsed into its own temp table.

Then in the WHERE clause you use a join of the two temp tables to see if there are any matches.