SQL Server Full Text Search

SQL Server full text search is pretty easy to set up and use.

Here are the necessary steps.

 

1) Create a table to contain the text to be searched. Searchable column should be varbinary(max)


create table dbo.FullText(
    FileID bigint not null identity(1,1),
    FileDescription varchar(1000) not null,
    FileTypeExtension varchar(25) not null default '.txt',
    FileContents varbinary(max) null,
    constraint pk_FullText primary key clustered (FileID)
)

2) Create a search catalog

CREATE FULLTEXT CATALOG FullTextInfo WITH ACCENT_SENSITIVITY = OFF
3) Create a fulltext index


CREATE FULLTEXT INDEX ON FullText
(FileDescription , FileContents TYPE COLUMN FileTypeExtension LANGUAGE 1033) 
KEY INDEX pk_FullText 
ON FullText
WITH STOPLIST = SYSTEM

4) Search!


SELECT FileDescription ,cast(FileContents  as varchar(max)) 'FileContents'
FROM FullText
WHERE  CONTAINS(FileContents, 'TextToFind')

4a) To see contents of fulltext index


SELECT display_term, column_id, document_count
FROM sys.dm_fts_index_keywords 
  (DB_ID('mydb'), OBJECT_ID('FullText'))
 

 

Find all files that contain the search term “TextToFind” but not within 130 search terms of “TextToAvoid


SELECT FileDescription ,cast(FileContents as varchar(max)) 'ScriptContents'
FROM FullText
WHERE  CONTAINS(FileContents, 'TextToFind')
and FileDescription not in (
SELECT  FileDescription 
FROM FullText
WHERE  CONTAINS(FileContents, 'near((TextToAvoid,TextToFind),130,true)')
)