Query with SQL Server Full-Text Search

In order to search we use the built-in functions: contains, freetext, containstable and freetexttable.

Let’s see it in action.

Contains

SELECT Title, Text, * FROM [dbo].[Post] where contains(Title, 'phone')
SELECT Title, Text, * FROM [dbo].[Post] where contains(Title, '"ph*"')
SELECT Title, Text, * FROM [dbo].[Post] where contains(*, '"ph*" or "mobile"')
SELECT Title, Text, * FROM [dbo].[Post] where contains(*, '"phone" NEAR "best"')
SELECT Title, Text, * FROM [dbo].[Post] where contains(*, 'FORMSOF(INFLECTIONAL, buy)')

The query on line #1 will return posts where the word “phone” appears on the title. It’s very similar to a like clause but it will perform more efficient than a like. The query #2 will return rows where title contains words that start with “ph”, again very similar to like ‘ph%’.

Line #3 will return records where any of the columns in the full-text search index (in this case Title and Text) contains a word that starts with “ph” or contains the word “mobile”.

Query in line #4 returns records where the Title or Text columns has the word “phone”near the word “best”. Now things are getting better right? Let’s continue.

The last query will get us any row where the Title or Text columns contain the word “buy” in any tense, or in singular or plural in case of a noun. If there is a post that uses the words: buy or bought, it will return them.

Freetext

SELECT Title, Text, * FROM [dbo].[Post] where freetext(Title, 'phone')

Freetext will match the words with ‘phone’ by it’s meaning, not the exact word. It will also compare the inflectional forms of the words, similar to what the last query of the Contains did. In this case it will return any title that has ‘mobile’, ‘telephone’, ‘smartphone’, etc.

Containstable & Freetexttable

These functions return a table with values of relevance (RANK column) that indicate for each record, how well any word did match with what we are searching for. Freetexttable match by meaning and not only by word.

select p.Title, c.Rank, * from [dbo].[Post] as p inner join
    containstable(Post, Title, 'phone') as c
    on p.Id = c.[Key]
select p.Title, c.Rank, * from [dbo].[Post] as p inner join
    freetexttable(Post, Title, 'best') as c
    on p.Id = c.[Key]