SQL Search Query using keywords

Question

I currently have a SQL query that returns results based on a dynamic number of keywords passed in.

I convert the list of keywords into a table and join to it.

    SELECT * FROM Table1
    INNER JOIN
        dbo.udf_List2Table(@Keywords, ',') ON (Field1 LIKE '%'+Keyword+'%')

This is working fine but it returns all rows that contain any of the keywords that are suppiled. What I would like to do is return all rows that contain all of the keywords supplied.

I'm pretty sure you can't do this using a JOIN. Does anyone have any suggestions on how I could do this? I'm trying to avoid dynamic SQL.

Thanks

Answer 1

Try

SELECT Field1 FROM Table1
INNER JOIN
    dbo.udf_List2Table(@Keywords, ',') ON (Field1 LIKE '%'+Keyword+'%')
GROUP BY Field1
HAVING COUNT(Keyword) = n

to match n keywords

Answer 2

SELECT  *
FROM    Table1
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    dbo.udf_List2Table(@Keywords, ',')
        WHERE   field1 NOT LIKE '%' + Keyword + '%'
        )

This can be done more efficiently if you create a FULLTEXT index on field1 and make your @Keywords doublequote enclosed and AND separated:

SET @Keywords = '"cabbages" AND "kings"'
SELECT  *
FROM    table1
WHERE   CONTAINS(Field1, @Keywords)

Answer 3

May be you can do following tweak.

DECLARE @recordcount int
SELECT @recordcount = count(1) from dbo.udf_List2Table(@Keywords, ',')
select t1.Field1
from Table1 t1
where @recordcount = (select count(1) from dbo.udf_List2Table(@Keywords, ',') k where t1.Field1 like '%' + k.Keyword + '%')