Most effective way to check sub-string exists in comma-separated string in SQL Server -

i have comma-separated list column available has values

product1, product2, product3 

i need search whether given product name exists in column.

i used sql , working fine.

select *  productslist  productname '%product1%' 

this query working slowly. there more efficient way can search product name in comma-separated list improve performance of query?

please note have search comma separated list before performing other select statements.

user defined functions comma separation of string

create function [dbo].[breakstringintorows] (@commadelimitedstring   varchar(max)) returns   @result table (column1   varchar(max)) begin         declare @intlocation int         while (charindex(',',    @commadelimitedstring, 0) > 0)         begin               set @intlocation =   charindex(',',    @commadelimitedstring, 0)                     insert   @result (column1)               --ltrim , rtrim ensure blank spaces   removed               select rtrim(ltrim(substring(@commadelimitedstring,   0, @intlocation)))                  set @commadelimitedstring = stuff(@commadelimitedstring,   1, @intlocation,   '')          end         insert   @result (column1)         select rtrim(ltrim(@commadelimitedstring))--ltrim , rtrim ensure blank spaces removed         return  end   declare @productname nvarchar(max)  set @productname='product1,product2,product3' select * product [productname] in(select * [dbo].[![enter image description here][1]][1][breakstringintorows](@productname))