TSQL - Partial Matching using LIKE for multiple values

You can create a temp table as

'CREATE TABLE #Pattern (
      SearchItems VARCHAR(20)
    );'

Side note: Make sure you check if the temp table exists to avoid errors. Now you can insert your search words to the temp table as

'INSERT 
    INTO #Pattern 
    VALUES 
        ('% APPLE %'),
        ('% ORANGE %'),
        ('% BANANA %');'

Now using this temp table, Search your table using a INNER JOIN like

'SELECT *
 FROM Store
 INNER JOIN #Pattern
    ON Store.Fruits LIKE SearchItems
'

As a note, Temp Tables are something I try to avoid mostly, but here it comes handy, and the case I was using this solution was not demanding on performance. Rather it made it easier to keep the ever growing searchItems maintained.

Hope this works for others too.

It could be as simple as:

SELECT
  *
FROM
  store
WHERE
  fruits LIKE 'apple%'
  OR fruits LIKE 'orange%'
  OR fruits LIKE 'pear%'

Try this, but performance will not be great

declare @parm varchar(200)
set @parm = ','+'apple,orange,pear'+','

select * from store where charindex(fruit,@parm) > 0

Found an answer here on how to use XML to split.

I'll just insert the values into a table and then query it.

http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx

Try this (Full-Text Searching):

SELECT  *
FROM store
WHERE CONTAINS(fruits , '"apple*" OR "orange*" OR "pear*"')

And here is the help to implement:

http://msdn.microsoft.com/en-us/library/ms142571.aspx

There is a way to do it in SQL, but it is quite complicated. Assuming you can live with matching a string of up to, say, three fruit names, you proceed in the following manner.

We assume that @fruits is the varchar variable containing the list of fruits, to which we append more comma delimiters (in case it contains less than three fruit names):

declare @fruits varchar(80);
set @fruits = <list of fruits passed in> + ',_,_,_,';

The following equations are not SQL, but the math behind the substring operations we will need for the like expressions:

NOTE: NOT SQL

First fruit word:
p1 = charindex(',', @fruits)            << position of ',' delimiter
v1 = substring(@fruits, 0, p1-1) + '%'  << fruit word we seek
r1 = substring(@fruits, p1+1)           << remainder of string

Second fruit word:    
p2 = charindex(',', r1)
v2 = substring(r1, 0, p2-1) + '%'
r2 = substring(r1, p2+1)

Third fruit word:
p3 = charindex(',', r2)
v3 = substring(r2, 0, p3-1) + '%'
r3 = substring(r2, p3+1)

...and so on...

Now we substitute the first values of p1, v1, and r1 into the second set of equations for p2, v2, and r2. And likewise, we substitute those second set of values into the third set, and so on. We end up with these monstrosities for v1, v2, and v3:

v1 = substring(@fruits, 0, charindex(',', @fruits)-1) + '%'
v2 = substring(substring(@fruits, charindex(',', @fruits)+1), 0, charindex(',', substring(@fruits, charindex(',', @fruits)+1))-1) + '%'
v3 = substring(substring(substring(@fruits, charindex(',', @fruits)+1), charindex(',', substring(@fruits, charindex(',', @fruits)+1))+1), 0, charindex(',', substring(substring(@fruits, charindex(',', @fruits)+1), charindex(',', substring(@fruits, charindex(',', @fruits)+1))+1))-1) + '%'

These are the first three LIKE values we need to look for:

select * from fruits
where fruit like <v1>
   or fruit like <v2>
   or fruit like <v3>

Fully expanded, the query is:

select * from fruits
where fruit like substring(@fruits, 0, charindex(',', @fruits)-1) + '%'
   or fruit like substring(substring(@fruits, charindex(',', @fruits)+1), 0, charindex(',', substring(@fruits, charindex(',', @fruits)+1))-1) + '%'
   or fruit like substring(substring(substring(@fruits, charindex(',', @fruits)+1), charindex(',', substring(@fruits, charindex(',', @fruits)+1))+1), 0, charindex(',', substring(substring(@fruits, charindex(',', @fruits)+1), charindex(',', substring(@fruits, charindex(',', @fruits)+1))+1))-1) + '%'

We can do more work to extract the 4th word, 5th word, 6th word, and so on as far as we like. But each further vN value gets far more complicated than the previous.

Note: I have not tried this solution, I have only proved it mathematically.