SQL Server 2012 – Full-Text Search for Keyword Existence

full-text-searchsql serversql-server-2012

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
    )

Best Answer

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)
Related Question