MySQL MATCH() AGAINST() Equivalent for SQL Server
mysql, sql, sql-server, sql-server-2000
Solution
This definitely sounds like a case for implementing Full-Text Search. It sounds like the algorithm you're after is much more complex than those offered by `CHARINDEX`, `PATINDEX` and `LIKE`, which are very simple on/off type results (either the string contains the searched expression or it doesn't).
The official Microsoft documentation for Full-Text Search starts here. This article may also be useful, as well as this MSDN article, "Improve the performance of full-text indexing", and some of these Full-Text Search tips on mssqltips.com.
The Full-Text keywords you're probably most interested in are `CONTAINS` and `CONTAINSTABLE`. This page describes, among other things, how CONTAINSTABLE applies ranking.
Problem
Is there a way that this full-text searching query could be translated from MySQL into SQL Server? ``` SELECT *, MATCH(title) AGAINST('My Title' IN BOOLEAN MODE) AS score FROM books WHERE MATCH(title) AGAINST('My Title' IN BOOLEAN MODE) ORDER BY score DESC, books.title ASC ``` If it helps, I am specifically using SQL Server 2000. I don't have the option of using a more recent version. :S Here is a sample scenario. Create the table: ``` -- -- Table structure for table `books` -- CREATE TABLE IF NOT EXISTS `books` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `title` (`title`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; -- -- Index the title column -- ALTER TABLE `books` ADD INDEX ( `title` ) -- -- Dumping data for table `books` -- INSERT INTO `books` (`id`, `title`) VALUES (1, 'My Title'), (2, 'My Title'), (3, 'Not My Title'), (4, 'Other Title'), (5, 'Not Related'), (6, 'Not Related Either'); ``` Execute the query: ``` SELECT *, MATCH(title) AGAINST('My Title' IN BOOLEAN MODE) AS score FROM books WHERE MATCH(title) AGAINST('My Title' IN BOOLEAN MODE) ORDER BY score DESC, books.title ASC ``` Here is what is returned: ``` id title score 1 My Title 1 2 My Title 1 3 Not My Title 1 4 Other Title 1 ``` Thank you for your time.