title: Filter results in SQL query for search
body: <p>I am trying to write an SQL query to filter records from products table for search depending on the search text.
  The text will have multiple words separated by space(e.g. Samsung s6) </p>
  
  <p>The problem is the results should contain all the words in the result.</p>
  
  <p>Now the records can have names like</p>
  
  <ol>
  <li>Samsung s6</li>
  <li>Samsung galaxy s6</li>
  <li>Samsung A5</li>
  <li>Samsung galaxy s6 edge</li>
  <li>Apple Iphone 6</li>
  </ol>
  
  <p>now for search text "samsung s6" should get records 1,2,4 in result but the way i tried is giving me 1,2,3,4 rows in result.</p>
  
  <p>my query is:</p>
  
  <pre><code>CREATE TABLE #TempSearch
  (
      ID INT IDENTITY(1,1),
      Value VARCHAR(200)
  )
  
  INSERT INTO dbo.#TempSearch
  SELECT * FROM CSVToTable(ISNULL(@ModelName,''),' ')
  
  SELECT 
      T1.*
  FROM
      &lt;table1&gt; T1 INNER JOIN #TempSearch T2 ON T1.Name LIKE CONCAT('%', T2.Value, '%')
  </code></pre>
  
  <p>I took the search string in a temp table which contains a row for each word (separated by space)</p>
  
  <p>then inner joined with the main table.</p>