Full Text Catalog

use Master
go
Create database FullTextExample
go
Alter Database FullTextExample
Add Filegroup FullTextCatalog
Go
Use FullTextExample
Go
Create Table Test
(
   testID int identity(1,1) primary Key,
   TestText Nvarchar(255)
)
Go
Insert into Test(TestText)
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')

Insert into Test(TestText)
Values('I go to work sadly'),
('I went to work yesterday'),
('I have gone to work every day'),
('going to work')

Select * From test

Create FullText Catalog TestDescription
on Filegroup FullTextCatalog
Go
Create FullText index on Test(TestText)
Key Index [PK__Test__A29BFBA819F32655]
on TestDescription
With Change_tracking auto
go
--find all instances that have the word "sad"
Select TestID, TestText 
From Test
Where FreeText(TestText, 'sad')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'success')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'men')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'relative')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'Success')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'is')

Select TestID, TestText 
From Test
Where Contains(TestText, '"success*"')


Select TestID, TestText 
From Test
Where Contains(TestText, ' Formsof (Inflectional, see)')

Select TestID, TestText 
From Test
Where Contains(TestText, ' Formsof (Inflectional, go)')

Select TestID, TestText 
From Test
Where Contains(TestText, 'Near ((work, day), max)')

Select * From Test