Match single words and phrases with precise or fuzzy (less precise) matching.
Match the meaning, but not the exact wording, of specified words, phrases, or sentences. Matches are generated if any term or form of any term is found in the full-text index of a specified column.
Here I will try to show an example of FREETEXT and FREETEXTTABLE in a simple way step by step—
2) Create Full-Text catalog—
CREATE FULLTEXT CATALOG SearchCatalog AS DEFAULT; GO
3) Now we have to create Full-Text INDEX
to table on which we want to enable Full-Text search option. To enable Full-Text INDEX on desired table need to have a Clustered INDEX. To Check it has clustered index or not select table name and press ALT + F1. If there is no clustered index found then create one clustered index on the Id column.
CREATE UNIQUE CLUSTERED INDEX [PK_CustomerInfo] ON [dbo].[CustomerInfo]([Id]) GO
Now create Full-Text INDEX—
CREATE FULLTEXT INDEX ON CustomerInfo ( FirstName, MiddleName, LastName, [Address], City ) KEY INDEX PK_CustomerInfo GO
5) We can enhance the query to show the result with match rank/score—
DECLARE @SearchParam NVARCHAR(250) SET @SearchParam = N'Gigi Matthew Brown'; SELECT ftt.RANK,p.* FROM CustomerInfo p INNER JOIN FREETEXTTABLE(CustomerInfo, *, @SearchParam) as ftt ON ftt.[KEY] = p.Id ORDER BY ftt.RANK DESC GO