FullText Search In SqlServer 2005

why fulltext search used?

  • Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a SQL Server Database. Unlike the LIKE predicate, which only works on character patterns, full-text queries perform a linguistic search against this data, operating on words and phrases based on rules of a particular language.
  • The performance benefit of using full-text search can be best realized when querying against a large amount of unstructured text data. A LIKE query (for example, ‘%microsoft%’) against millions of rows of text data can take minutes to return; whereas a full-text query (for ‘microsoft’) can take only seconds or less against the same data, depending on the number of rows that are returned.

Keyword Used In FullText Search

   1)FreeText 2)FreeTextTable 3)Contains 4) ContainsTable

 

1)FreeText

  • Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition.

2)FreeTextTable

  • Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can be referenced in the FROM clause of a SELECT statement like a regular table name.

3)Contains

  • A word or phrase.
  • The prefix of a word or phrase.
  • A word near another word.
  • A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
  • A word that is a synonym of another word using thesaurus (for example, the word metal can have synonyms such as aluminum and steel).

4)ContainsTable

  • Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.
  • Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.

 

I am use the sql server version Standard Edition.

 

Steps:

 

1) Enabled the fulltext on the database using the sp.

   EXEC sp_fulltext_database 'enable'

2) Create Table in Database 

 

CREATE TABLE [dbo].[UserDetail](

[UserDetailId] [int] IDENTITY(1,1) NOT NULL,

[UserId] [int] NULL,

[Address1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Address2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 CONSTRAINT [PK_UserDetail] PRIMARY KEY CLUSTERED

(

[UserDetailId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

3) Insert Record Into Table

 

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(1,’nava india’,’programming’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(3,’nava america’,’Developing’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(5,’juna india’,’Testing ‘)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(6,’juna america’,’QA’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(8,’india’,’PM’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(3,’japan’,’Developing’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(3,’nava japan’,’Developing’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(1,’juna japan’,’programming’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(1,’america nava’,’programming’)

4) Enabled Full Text in Table

    Right Click On Table

     * Select Full Text Index

       * Define Full Text Index

 

Click Next –> Next

 

 

–here you select Field where you apply full text search field.

select both the field and press Next.

In Next Select automatic and Press Next.

In Next Select Full Text Catalog and Press Next.

Press Next –> Next –> Press Finish Button

 

5) Query Using FullText Search Keyword

5.1) FreeText

  • select * from userdetail where FreeText(Address1,‘nava juna america’)

          Search From Address1 Field where keyword like nava,juna,america.

  • select*fromuserdetail where FreeText(*,‘nava india QA’)

Search From Address1,Address2 Field where keyword like nava,india,QA

5.2 ) FreeTextTable

  • SELECT  * FROM UserDetail AS FT_TBL  INNER JOIN FREETEXTTABLE(UserDetail,Address1,‘nava juna’) AS KEY_TBL

         ON FT_TBL.UserDetailId = KEY_TBL.[KEY];

         –TableName -UserDetail

         –Column Name – Address1

         –Search Keyword -words

Search From Address1 field where keyword like nava juna

  • SELECT * FROM UserDetail AS FT_TBL INNER JOIN FREETEXTTABLE(UserDetail,*, 

    ‘nava PM’) AS KEY_TBL ON FT_TBL.UserDetailId = KEY_TBL.[KEY];

     Search From Address1,Address2 field where keyword like nava,PM

 

5.3)CONTAINS

  • select * from userdetail where Contains(address1,'nava')
search from Address1 where keyword like nava
select * from userdetail where Contains(address1,'nava juna')
you can get the error if you write this with contain keyword.

  • select * from userdetail where Contains(address1,‘”nava” or “india”‘)

          Search From Address1 where keyword like nava or india

 

  • select * from userdetail where Contains(address1,‘”na*”‘)
  • select*fromuserdetail where Contains(*,‘”nava” or “india” or “PM”‘)

         Search From Address1,Address2 where keyword like nava or india or PM

  • select * from userdetail where Contains(*,‘ISABOUT (nava weight (0.8))’)

 

 

Thanks.

 

Reference Site :

MSDN Help

http://msdn.microsoft.com/en-us/library/ms345119.aspx

Advertisements

5 comments

  1. I have used full text search in sql server.
    But i found lucene open source search engine was very useful

    create lucene index in c#
    search lucene index in c#

  2. Hi,

    I want to use Full text search in sql. But I am not seeing any option on my table, after right clicking for full text index. I m using sql server express 2005 edition. kinldy help me.

  3. Yasser Ogier · · Reply

    This step by step explanation of setting up “Full Text Searching” is brilliant. i got my issues sorted out in a few minutes by following these easy steps.

    The Query examples are simple and straight forward yet in detail.

    Thanx Man. Brilliant Job…

  4. hi

    how to create a full text index in sql server 2005. Because I right click the table but no option in full text index properties. I installed the full text indexing . Please help me

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s