How to select rows that contain substrings from another table

_{Friday, March 23, 2007 2:40 PM}

I can't even think of a good way to search for this information, so let me try to ask the question as clearly as I can.

My database contains two tables: "ProductInfo" with many fields of information, and "KeywordList" which is a list of several thousand specific keywords.  I want to create a query that retrieves all rows in the ProductInfo table which contain any of the keywords in the KeywordList as a substring within any of several fields.

I can make this work for exact matching -- as in this example:

  SELECT * FROM ProductInfo WHERE Name = ANY (Select Keyword from KeywordList)

(Which retrieves all rows where the "Name" field is an exact match to any phrase in the keyword list)

But unfortunately, I don't want exact matches, but a substring match: 

  SELECT * FROM ProductInfo WHERE Name LIKE ANY '%{Select Keyword from KeywordList}%'

Can someone suggest a SQL statement that will retrieve the rows that I am seeking here?

_{Friday, March 23, 2007 3:04 PM}

You could use LIKE statement in join predicate:

use demo

create table #ProductList

(

name varchar(100)

)

go

create table #KeywordList

(

word varchar(100)

)

go

insert into #ProductList values('Cool device')

insert into #ProductList values('Super software')

insert into #ProductList values('Cool software')

insert into #ProductList values('Bad device')

insert into #KeywordList values('Cool')

insert into #KeywordList values('software')

select * from #ProductList p join

#KeywordList k on (p.Name Like '%'+k.word+'%')

_{Friday, March 23, 2007 4:31 PM}

Whilst this is fine, I feel the need to point out that for any large ProductList table this is likely to run very slowly.  Despite starting to sound like a broken record I would suggest that you consider using Free Text Search (FTS). 

I am assuming that it is possible to use CONTAINS and FREETEXT in a JOIN condition (nothing in the documentation says no - but I have never done it).  In which case you would still use a similar construct to Konstantin's.

SELECT P.*
FROM ProductList P
  INNER JOIN KeywordList K
    ON CONTAINS(P.Name, K.Word)

One problem I can see with this though is that if the name contains 2 of the words you may get it twice (though it will also be a problem with Kostantin's version).

Another advantage is that FTS supports placing a list of columns in the search term (or even * for all FTS registered columns in the table).

Even if you are using SQL Server Express 2005 you can get Full Text Search (and SQL Server Management Studio Express and Reporting Services).  You need to download the "SQL Server 2005 Express Edition with Advanced Services" rather than the standard version.  This is 234Mbytes as opposed to 37M (+ 43M for Management Studio Express on its own) but you can then use FTS apparently.  It is another option on the download page.