title: Like Operator with comma-separated values in SQL Server
body: <p>For example, this is my original table</p>
  
  <pre><code>SELECT id, testName FROM tblTest1
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/V1oRi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/V1oRi.png" alt="enter image description here"></a></p>
  
  <p>Whenever my comma-separated values are in proper way, then I got my result based on my original table.</p>
  
  <p>See this below image:</p>
  
  <pre><code>DECLARE @Test VARCHAR(100)
  SET @Test='General, Narcotic'
  
  SELECT id, testName 
  FROM tblTest1 a 
  WHERE ISNULL(a.testName, '') LIKE (CASE WHEN isnull(@Test,'') = '' THEN isnull(a.testName,'') 
                                          ELSE '%'+@Test+'%' END)               
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/e4UBD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/e4UBD.png" alt="enter image description here"></a></p>
  
  <p>But when I am changing the original position of comma separated values, I am not getting any output. </p>
  
  <p>See this below image:</p>
  
  <pre><code>DECLARE @Test VARCHAR(100)
  SET @Test='Narcotic,General'
  
  
  SELECT id, testName FROM tblTest1 a 
  WHERE isnull(a.testName,'') Like (CASE WHEN isnull(@Test,'') = '' THEN isnull(a.testName,'') 
  ELSE '%'+@Test+'%' END)
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/1ax9m.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1ax9m.png" alt="enter image description here"></a></p>
  
  <p>So, I want the query that I want to pick all the rows whenever my comma separated string the in that row (including the changing comma seprated values not based with original table with <code>@paramer</code> is null then pick all the rows) </p>