TSQL - Partial Matching using LIKE for multiple values

Question

I would like to seek your advice whether any function in SQL server that allow me to perform partial matching for a list of values ?

The entire string that need to be matched will be passed in via store procedure.

I am trying to find other alternative before writing my own function to split the string by comma and then union all the results before return the data to the program.

For example, I would pass in the following string into my TSQL

apple,orange,pear

in my WHERE clause it should match

select * from store where fruits like 'apple%'
select * from store where fruits like 'orange%'
select * from store where fruits like 'pear%'

Can I achieve the above results in a single SQL statement rather than writing function to break each string ?

Data in my Table

apple red
apple green
orange sweet
orange sour
pear big
pear small

So, when I passed in the string "apple,pear" , I need to return

apple red
apple green
pear big
pear small

Answer 1

It could be as simple as:

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

Answer 2

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.