sql server 2005 - select records from tbl A contained WITHIN a text field of tbl B

Question

Hi,

I'm trying to work out a SQL Select in MS SQL 2005, to do the following:

TABLE_A contains a list of keywords... asparagus, beetroot, beans, egg plant etc (x200). TABLE_B contains a record with some long free text (approx 4000 chars)...

I know what record within TABLE_B I am selecting (byID).

However I need to get a shortlist of records from TABLE_A that are contained WITHIN the text of the record in TABLE_B.

I'm wondering if SQLs CONTAINS function is uselful... but maybe not.

This needs to be a super quick query.

Cheers

Answer 1

I would try this

select keyword from table_a, table_b where table_b.text like '%' + keyword + '%' and table_b.Id = '111'

Answer 2

It will never be super quick because of the LIKE and wildcard at each end. You can not index it and there are no whizzy tricks. However, because you have already filtered TableB then it should be acceptable. If you had a million rows in tableB, you could go for coffee while it ran

SELECT
    A.KeyWordColumn
FROM
    TableA A
    JOIN
    TableB B ON B.BigTextColumn LIKE '%' + A.KeyWordColumn+ '%'
WHERE
    B.ByID = @ID --or constant etc

CONTAINS can be used if you have full text indexing: but not for a normal SQL query