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:

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…:

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”

ID WordOrPhrase
12 Page
15 Page number
17 Page and Chapter Numbers

Next, return results that contain the word “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”.

ID WordOrPhrase
11 Chapter number
15 Page number

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

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”.

ID WordOrPhrase
11 Chapter number
15 Page number
16 Number of pages
17 Page and Chapter Numbers

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

ID WordOrPhrase
12 Page
15 Page number
17 Page and Chapter Numbers

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

>

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.

CJayMead's SQL Server, Endeca, MS Office Help © 2018