Most effective way to check sub-string exists in comma-separated string in SQL Server
<p>I have a comma-separated list column available which has values like </p>

<pre><code>Product1, Product2, Product3
</code></pre>

<p>I need to search whether the given product name exists in this column.</p>

<p>I used this SQL and it is working fine.</p>

<pre><code>Select * 
from ProductsList 
where productname like '%Product1%'
</code></pre>

<p>This query is working very slowly. Is there a more efficient way I can search for a product name in the comma-separated list to improve the performance of the query?</p>

<p>Please note I have to search comma separated list before performing any other select statements.</p>