Tuesday, December 21, 2010

Freetext in Sqlserver

FreeText using Full Text Indexing in sql server

Suppose we need to search for a string or for some words in a column in sql server we have one good option that is FREETEXT. by using FREETEXT we can search the data inside a column easily.
you have a column named Question, in that you want to search for "dotnet asp.net c#"
then you can use FREETEXT which searches the Question column with the "dotnet", "asp.net',"c#".

If the column has any of the 3 then the column will be selected.
For that we need to do following...

1. Create a CATALOG table in database.

2. Create a Index on table for which you want to apply FULL TEXT INDEX.
(index may be primary key, unique key)
CREATE UNIQUE INDEX IndexName ON tablename(column);

3. Create a FULL TEXT INDEX on table based on the index created in (2)
CREATE FULLTEXT INDEX ON tablename(column) KEY INDEX IndexName

4.Use functions like FREETEXT, CONTAINS... for searching the database.
select * from tablename where FREETEXT(column,'search string') order by sno desc
or for Sql parameters
select * from tablename where FREETEXT(column,@string) order by sno desc

No comments:

Post a Comment