SQL: Full Text Search

This post is work in progress, but I am looking into the use of Full Text Search within SQL Server.

Firstly, I created a “Test” table, and populated it with same data, as you can see from the select statement below:

SELECT * FROM Test
ID WordOrPhrase
1 Book
2 Books
3 Book’s
4 Books’
5 Booking
6 Bookings
7 Booked
8 Chapter
9 Chapters
10 Chapters of a book
11 Chapter number
12 Page
13 Pages
14 Pages of a book
15 Page number
16 Number of pages
17 Page and Chapter Numbers

Next, I select records that are “like” …Page…:

SELECT * FROM Test
WHERE WordOrPhrase LIKE '%Page%'
ID WordOrPhrase
12 Page
13 Pages
14 Pages of a book
15 Page number
16 Number of pages
17 Page and Chapter Numbers

Then try using CONTAINS. Note that this does the equivalent of “Page …”, “… Page”, or “… Page …”, and doesn’t return “Pages”

SELECT * FROM Test
WHERE CONTAINS(WordOrPhrase, 'Page')
ID WordOrPhrase
12 Page
15 Page number
17 Page and Chapter Numbers

Next, return results that contain the word “Page” OR “Chapter”.

SELECT * FROM Test
WHERE CONTAINS(WordOrPhrase, 'Page OR Chapter')
ID WordOrPhrase
8 Chapter
11 Chapter number
12 Page
15 Page number
17 Page and Chapter Numbers

Next, get results for “Page” or “Chapter”, along with “Number”, e.g. “Page Number” or “Chapter Number”.

SELECT * FROM Test
WHERE CONTAINS(WordOrPhrase, '(Page OR Chapter) AND Number')
ID WordOrPhrase
11 Chapter number
15 Page number

Now return results for variations of “Page”, such as “Pages”

SELECT * FROM Test
WHERE CONTAINS(WordOrPhrase, 'FORMSOF(INFLECTIONAL,Page)')
ID WordOrPhrase
12 Page
13 Pages
14 Pages of a book
15 Page number
16 Number of pages
17 Page and Chapter Numbers

Now look for variations of “Page” or “Chapter”, along with “Number”. Notice that we now get “Number of Pages” and “Page and Chapter Nubmers”.

SELECT * FROM Test
WHERE CONTAINS(WordOrPhrase, '((FORMSOF(INFLECTIONAL,Page) OR FORMSOF(INFLECTIONAL,Chapter)) AND FORMSOF(INFLECTIONAL,Number))')
ID WordOrPhrase
11 Chapter number
15 Page number
16 Number of pages
17 Page and Chapter Numbers
 SELECT * FROM Test
WHERE FREETEXT(WordOrPhrase, '(Page OR Chapter) AND Number')
ID WordOrPhrase
8 Chapter
9 Chapters
10 Chapters of a book
11 Chapter number
12 Page
13 Pages
14 Pages of a book
15 Page number
16 Number of pages
17 Page and Chapter Numbers
 SELECT * FROM Test
WHERE CONTAINS(WordOrPhrase, 'FORMSOF(THESAURUS,Page)')
ID WordOrPhrase
12 Page
15 Page number
17 Page and Chapter Numbers
 SELECT * FROM Test
WHERE CONTAINS(WordOrPhrase, 'FORMSOF(INFLECTIONAL,Book)')
ID WordOrPhrase
1 Book
2 Books
3 Book’s
4 Books’
5 Booking
7 Booked
10 Chapters of a book
14 Pages of a book
 SELECT * FROM Test
WHERE FREETEXT(WordOrPhrase, 'Books')
>

ID WordOrPhrase
1 Book
2 Books
3 Book’s
4 Books’
5 Booking
7 Booked
10 Chapters of a book
14 Pages of a book

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.