title: TSQL - Partial Matching using LIKE for multiple values
body: <p>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 ?</p>
  
  <p>The entire string that need to be matched will be passed in via store procedure.</p>
  
  <p>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.</p>
  
  <p>For example, I would pass in the following string into my TSQL</p>
  
  <blockquote>
    <p>apple,orange,pear</p>
  </blockquote>
  
  <p>in my <code>WHERE</code> clause it should match</p>
  
  <pre><code>select * from store where fruits like 'apple%'
  select * from store where fruits like 'orange%'
  select * from store where fruits like 'pear%'
  </code></pre>
  
  <p>Can I achieve the above results in a single SQL statement rather than writing function to break each string ?</p>
  
  <blockquote>
    <p>Data in my Table</p>
  </blockquote>
  
  <pre><code>apple red
  apple green
  orange sweet
  orange sour
  pear big
  pear small
  </code></pre>
  
  <p>So, when I passed in the string "apple,pear" , I need to return </p>
  
  <pre><code>apple red
  apple green
  pear big
  pear small
  </code></pre>