Reading items with LIKE in a function that splits

I have a query that uses a function called "My_Splitter" when I pass a string like

"this is an example", it returns

this
is
an
example

The problem I am having in my query is that I need to take each of those values and do something like

select value from My_Splitter(@MyString) where value LIKE '%this%' or value LIKE '%is%' or value LIKE '%an%' or value like '%example%'

you get my idea... here is my query

 select                 
  DISTINCT(pr.myID)            
 from                 
  myProducts pr               
  left JOIN pcategory pc ON pr.ProductId = pc.Prod开发者_如何学运维uctId                
  left JOIN category ca on ca.categoryid = pc.categoryid       
  left join sNumber zs on zs.productid=pr.productid                
 where                 
  pr.activated = 1               
  and pr.PortalId = @PortalId                
  and (pr.Name like '%' + @Keyword + '%'  
  or pr.Name in (select value from SC_Splitter(@Keyword,' ')) //PROBLEM HERE

  or pr.myTitle LIKE '%' + @Keyword + '%' 

  or pr.PRnum LIKE '%' + @Keyword + '%'       
  or zs.snum like   '%' + @Keyword + '%' 

I WISH I COULD JUST DO

or pr.Name LIKE (select value from SC_Splitter(@Keyword,' ')) and it would take any value from @Keywprd and so a LIKE search on each :( lol

If you use Full-Text Search in Microsoft SQL Server, the difficulty goes away, and your searches will be orders of magnitude faster.

select                 
  DISTINCT(pr.myID)            
 from                 
  myProducts pr               
  left JOIN pcategory pc ON pr.ProductId = pc.ProductId                
  left JOIN category ca on ca.categoryid = pc.categoryid       
  left join sNumber zs on zs.productid=pr.productid                
 where                 
  pr.activated = 1               
  and pr.PortalId = @PortalId                
  and (CONTAINS(pr.Name, @Keyword)  
  or FREETEXT(pr.Name, @Keyword) //NO PROBLEM HERE
  ...

Another possibility is to use CROSS APPLY.

 select
  DISTINCT(pr.myID)
 from
  myProducts pr
  left JOIN pcategory pc ON pr.ProductId = pc.ProductId
  left JOIN category ca on ca.categoryid = pc.categoryid
  left join sNumber zs on zs.productid=pr.productid

  CROSS APPLY SC_Splitter(@keyword,' ') AS sentance

 where                 
  pr.activated = 1               
  and pr.PortalId = @PortalId                

  and (pr.Name like '%' + @Keyword + '%'  
      OR pr.name LIKE '%' + Sentance.Value + '%')

  or pr.myTitle LIKE '%' + @Keyword + '%'     
  or pr.PRnum LIKE '%' + @Keyword + '%'       
  or zs.snum like   '%' + @Keyword + '%' 

i suggest restructuring your splitter to a new function that can accept two strings, the name and the 'sentence'

then that function would loop over the parsed sentence to see if it matches the name and return a 1 if found, and a 0 if not.

then your query would simply have a where my_new_function( name, sentence ) = 1 line

This fixes the issue with your solution., but Bill Karwin's answer is the correct solution for the problem.

To save time and more questions, this can't be optimised. The main reason is leading wildcards. Use Full Text Search if you want "fast" as well as "working"

Basically, JOIN using LIKE

 select                 
  DISTINCT(pr.myID)            
 from                 
  myProducts pr               
  left JOIN pcategory pc ON pr.ProductId = pc.ProductId                
  left JOIN category ca on ca.categoryid = pc.categoryid       
  left join sNumber zs on zs.productid=pr.productid  

  JOIN 
  SC_Splitter(@Keyword,' ') CSV ON pr.Name LIKE '%' + CSV.value + '%'

 where                 
  pr.activated = 1               
  and pr.PortalId = @PortalId                
  and

--fix your parenthesis after this. 
--I'm not sure what was intended above

 (pr.Name like '%' + @Keyword + '%'  

  or pr.myTitle LIKE '%' + @Keyword + '%' 

  or pr.PRnum LIKE '%' + @Keyword + '%'       
  or zs.snum like   '%' + @Keyword + '%' 
 )