0

I have a table of keywords and I want to query another table and return rows where a column contains at least one keyword.

Is it possible query like that using full text indexes?

If not is there a more efficient way than the one below - dynamic SQL perhaps?

SELECT
    *

FROM
    dbo.Notes AS NotesContainingKeyword

WHERE
    EXISTS
    (
        SELECT
            *

        FROM
            dbo.Notes
            CROSS JOIN dbo.Keywords

        WHERE
            Notes.Note LIKE '%' + Keywords.Word '%'
            AND Notes.Id = NotesContainingKeyword.Id
    )

1 Answer 1

0

Concatenating the keywords with an OR(|) separator into a variable appears to work.

DECLARE
    @Keywords NVARCHAR(4000)

SELECT
    @Keywords = STUFF((
                    SELECT
                        '|' + Word

                    FROM
                        dbo.Keywords

                    FOR XML PATH('')
                ), 1, 1, '')

SELECT
    *

FROM
    dbo.Notes

WHERE
    CONTAINS(Note, @Keywords)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.