Transact SQL – Full-text Search multiple words

Background

Wanted to provide a working example of how to construct multiple word Full-Text search in Transact SQL.

Transact SQL

Outline

Our phrase is tokenized, each word is quoted, and joined together using an and clause.

Code


use [bible]
go

declare @phrase varchar(300)

set @phrase = '("Love" and "Jesus")'

SELECT top 100
          tblKey.n
        , tblKJV.t
        , [chapterAndVerse]
            = cast(tblKJV.c as varchar(10))
                + ':'
                + cast(tblKJV.v as varchar(10))

FROM   [dbo].[kjv] tblKJV

inner join [dbo].[key_english] tblKey

    on tblKJV.b = tblKey.b

WHERE CONTAINS
        (
              tblKJV.t
            , @phrase
        )

order by
    tblKJV.[id]

GO

Output

query.and.jesus.love

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s