Full Text Catalog

Here is the Full text stuff we did in class. I am still not sure why SQL server seems to be so allergic to the word "want."

--Full Text Catalog
use Master
Create Database FullTextDatabase
Go
--add a filegroup
Alter Database FullTextDatabase
Add Filegroup FullTextCatalog
go
use FullTextDatabase
go
--add a table with some text
Create Table TextTest
(
   TestId int identity (1,1) primary key,
   TestNotes Nvarchar(255)
)
go
--insert text
Insert into TextTest(TestNotes)
Values('For test to be successful we must have a lot of text'),
('The test was not successful. sad face'),
('there is more than one test that can try a man'),
('Success is a relative term'),
('It is a rare man that is always successful'),
('The root of satisfaction is sad'),
('men want success')
go
Select * From TextTest
go
--create full text catalog
Create FullText Catalog TestDescription
on Filegroup FullTextCatalog
go
--Create a full text index
Create FullText index on textTest(TestNotes)
Key Index [PK__TextTest__8CC33160448E9751]
on TestDescription
With Change_tracking auto
go
--run queries on the full text catalog

--find all instances that have the word "sad"
Select TestID, TestNotes 
From TextTest
Where FreeText(TestNotes, 'sad')

--do the same with successful
Select TestID, TestNotes 
From TextTest
Where FreeText(TestNotes, 'successful')

Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, '"success"')

Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, '"want"')

--look for any words containing the letters "success"
--the * is a wildcard
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, '"success*"')

Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, '"want*"')

--looks for all grammatical forms of a word
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, ' Formsof (Inflectional, Person)')