Full Text Search in SQL.

We need Full Text Search in many cases such as To search most related articles and contents Stored in some data base.
Google, Bing and many other search engines follow some sort of searching technique which allows us to do a Full Text Search on the World Wide Web. This Article will Show you how to mimic this behavior in sql server.

Following is the Table on which we are going to perform Full Text Search.








In this Particular example Article_title and Keywords are the column we are going to Perform our  search for.

now first we need to create a catalog .
The catalog system uses SQL Server full-text catalogs to store and search catalog content. The system creates a full-text catalog for every catalog.
The free-text catalog contains the properties that are free-text searchable.

Below is an example to create Full Text Catalog

example:

GO
CREATE FULLTEXT CATALOG ArticleFTS AS DEFAULT;
GO

Now that we have created a Full Text Catalog for the database, we need to create a 

Full Text Index to make our previous table Full Text Searchable.

 

Below is the Code to do so.

example:

CREATE FULLTEXT INDEX ON Article (Article_title, Keywords TYPE COLUMN FileExtension LANGUAGE 1033) KEY INDEX PK_Article_ArticleID ON ArticleFTS

WITH STOPLIST = SYSTEM

Now as we have created a full text index for the table, we can accomplish Full Text Search as following .

   select                 *         from                 Article
   where        ISNULL(@Article_title,'')='' or
        (
         FreeText(Article_title,@Article_title)
         or FreeText(Keywords,@Article_title)
         or  Article_title like '%'+@Article_title+'%'
         or Keywords like '%'+@Article_title+'%'
         )


Below is a Procedure to do the Full Text Search.

example:

Create PROCEDURE [dbo].[prGet_Article_titleByArticle_title](
    @Article_title nvarchar(500)=null
    )
    as
    --IF (@Article_title IS NOT NULL)
    --BEGIN
        select
                Article_ID,Article_title,SUBSTRING(html, CHARINDEX('<p>', html)
, CHARINDEX('</P>',html) - CHARINDEX('<p>', html) + Len('</P>')) as html1
       
        from
                Article
        where
       
        ISNULL(@Article_title,'')='' or
        (
         FreeText(Article_title,@Article_title)
         or FreeText(Keywords,@Article_title)
         or  Article_title like '%'+@Article_title+'%'
         or Keywords like '%'+@Article_title+'%'
         )