How to use fulltext index search in Microsoft SQL Server and MVC

Home > Search > How-to
  by

The following commands can be issued to create a full-text search in SQL Server.

Select the database that is being used.

use database_name

 

Create a fulltext catalog. Replace catalog_name with a name for your catalog, such as MyCatalog. To keep things organized, a good catalog name is DatabaseName_Catalog.

create fulltext catalog catalog_name

 

Verify that the catalog has been created.

select * from sys.fulltext_catalogs

 

Use this command to confirm that full-text is installed. Under No column name, if there is a 1, full-text is installed. If there is a 0, full-text is not installed. If full-text is not installed, run the SQL Server installed, and select full-text.

select serverproperty('IsFullTextInstalled')

 

Alter the ID column of the table that you want to apply the full-text to, adding a constraint to the table, such as TableName_Constraint.

alter table table_name add constraint constraint_name unique (id)

 

If a mistake is made, use the following command to remove the unique constraint from the table.

alter table table_name drop constraint constraint_name

 

Use the following command to verify that the unique constraint has been applied to the correct database, table, and column.

select * from information_schema.table_constraints where constraint_type='unique'
select * from information_schema.constraint_column_usage

 

Use the create full index command to identify which columns you want to be searchable by the search engine.  In this example, use the same table name and contraint name that was used in the previous alter table command. Use the catalog_name that was used in the previous create fulltext catalog command.

create fulltext index on table_name (column1, column2, column3) key index constraint_name on catalog_name

 

To verify that the fulltext index has been applied to the correct columns, we first need to use the select * from information_schema.columns where table_name='table name' command to determine ordinal position of each column. Let's say that column2 has "2" as the ordinal position. We can then use the select * from sys.fulltext_index_columns command to verify that "2" is an fulltext indexed column.

select * from information_schema.columns where table_name='table name'
select * from sys.fulltext_index_columns

 

If you make a mistake when creating the fulltext index, you can remove the fulltext index using the drop command.

drop fulltext index on table_name

 

Stops words, such as if and the can cause issues at first, so let's disable the stop words.

alter fulltext index on table_name set stoplist = off

 

To test fulltext search, issue the following command.  Only records where the Title contains both the keyword test and one will be returned.  This is perfect.

select * from table_name where contains ( (column1,column2,column3), 'test and one')

 

After following the instructions in the article on how to create a search engine in MVC, searching with keywords test and one in your MVC application will produce a URL such as http://example.com/?searchBy=column1&search=test+one. This will search for the literal string "test+one", which is not what we want. Let's update /Controller/ExampleController.cs to search for two keywords. Notice this markup now has "keyword1" and keyword2". 

public ActionResult Index(string searchBy, string keyword1, string keyword2) {   
  if (searchBy == "article_title")    {       
    return View(db.Content.Where(x => x.article_title.Contains(keyword1) && x.article_title.Contains(keyword2)).ToList());    
  }   
  else    {       
    return View(db.Content.Where(x => x.article_title.StartsWith(search)).ToList());    
  } 
}

 

To ensure this works, we can use keyword1 and keyword2 in the URL, and we should get results where column1 has a record with both test and one (http://www.example.com/?searchBy=column1&keyword1=test&keyword2=one).

When typing text into search, we need search to associate the first word with keyword1 and the second word with keyword 2. The String.Split function can be used to split a string, in the Controllers/ExampleController.cs file.

public string Index()
 {
   string MyString = "Hello from MVC application";
   string[] words = MyString.Split(' ');
   return MyString;
 }

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments