full-text-search

Why doesn't SQL Full Text Indexing return results for words containing #?

For instance, my query is like the following using SQL Server 2005: SELECT * FROM Table WHERE FREETEXT(SearchField, 'c#') I have a full text index defined to use the column SearchField which returns results when using: SELECT * FROM Table WHERE SearchField LIKE '%c#%' I believe # is a special letter, so how do I allow FREETEXT to w...

How do you get leading wildcard full-text searches to work in SQL Server?

Note: I am using SQL's Full-text search capabilities, CONTAINS clauses and all - the * is the wildcard in full-text, % is for LIKE clauses only. I've read in several places now that "leading wildcard" searches (e.g. using "*overflow" to match "stackoverflow") is not supported in MS SQL. I'm considering using a CLR function to add regex...

How to enable Full-text Indexing in SQL Server 2005 Express?

I am trying to enable Full-text indexing in SQL Server 2005 Express. I am running this on my laptop with Vista Ultimate. I understand that the standard version of SQL Server Express does not have full-text indexing. I have already downloaded and installed "Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 2" ...

SQL Server Full Text Searching

I am currently working on an application where we have a SQL Server database and I need to get a full text search working that allows us to search people's names. Currently the user can enter a into a name field that searches 3 different varchar cols. First, Last, Middle names So say I have 3 rows with the following info. 1 - Phillip ...

Can you perform an AND search of keywords using FREETEXT() on SQL Server 2005?

There is a request to make the SO search default to an AND style functionality over the current OR when multiple terms are used. The official response was: not as simple as it sounds; we use SQL Server 2005's FREETEXT() function, and I can't find a way to specify AND vs. OR -- can you? So, is there a way? There are a number of re...

How do you full-text search multiple criteria on left-joined tables in SQL Server?

I have a query that originally looks like this: select c.Id, c.Name, c.CountryCode, c.CustomerNumber, cacc.AccountNumber, ca.Line1, ca.CityName, ca.PostalCode from dbo.Customer as c left join dbo.CustomerAddress as ca on ca.CustomerId = c.Id left join dbo.CustomerAccount as cacc on cacc.CustomerId = c.Id where c.CountryCode = 'XX' and ...

Best full text search alternative to ms sql, c++ solution

What is the best full text search alternative to ms sql? (which works with ms sql) I'm looking for something similar to Lucene and Lucene.NET but without the .NET and Java requirements. I would also like to find a solution that is usable in commercial applications. ...

How to implement a "related" degree measure algorithm?

I was going to Ask a Question earlier today when I was presented to a surprising functionality in Stackoverflow. When I wrote my question title stackoverflow suggested me several related questions and I found out that there was already two similar questions. That was stunning! Then I started thinking how I would implement such function...

Searching subversion history (full text)

Is there a way to perform a full text search of a subversion repository, including all the history? For example, I've written a feature that I used somewhere, but then it wasn't needed, so I svn rm'd the files, but now I need to find it again to use it for something else. The svn log probably says something like "removed unused stuff", ...

MS SQL FTI - searching on "n*" returns numbers

This seems like odd behaviour from SQL's full-text-index. FTI stores number in its index with an "NN" prefix, so "123" is saved as "NN123". Now when a user searches for words beginning with N (i.e. contains "n*" ) they also get all numbers. So: select [TextField] from [MyTable] where contains([TextField], '"n*"') Returns: MyTabl...

SQL Server Full-Text Search: Hung processes with MSSEARCH wait type

We have a SQL Server 2005 SP2 machine running a large number of databases, all of which contain full-text catalogs. Whenever we try to drop one of these databases or rebuild a full-text index, the drop or rebuild process hangs indefinitely with a MSSEARCH wait type. The process can’t be killed, and a server reboot is required to get thin...

How do I do full-text searching in Ruby on Rails?

I would like to do full-text searching of data in my Ruby on Rails application. What options exist? ...

Upgrade database from SQL Server 2000 to 2005 -- and rebuild full-text indexes?

I'm loading a SQL Server 2000 database into my new SQL Server 2005 instance. As expected, the full-text catalogs don't come with it. How can I rebuild them? Right-clicking my full text catalogs and hitting "rebuild indexes" just hangs for hours and hours without doing anything, so it doesn't appear to be that simple... ...

SQL Server Freetext match - how do I sort by relevance

Is it possible to order results in SQL Server 2005 by the relevance of a freetext match? In MySQL you can use the (roughly equivalent) MATCH function in the ORDER BY section, but I haven't found any equivalence in SQL Server. From the MySQL docs: For each row in the table, MATCH() returns a relevance value; that is, a similarity mea...

When should you use full-text indexing?

We have a whole bunch fo queries that "search" for clients, customers, etc. You can search by firstname, email, etc. We're using LIKE statements in the following manner: select * from customer where fname like '%someName%' Does full-text indexing help in the scenario? Edit: I failed to mention we're using MSSQL 2005. ...

What are some Search Servers out there?

I'm looking to find alternatives to Solr from the Apache Software Foundation. For those that don't know, Solr is an enterprise search server. A client application uses a web-services like interface to submit documents for indexing and also to perform search queries. Solr has other features built in like caching and replication. I belie...

LinqToSql and full text search - can it be done?

Has anyone come up with a good way of performing full text searches (FREETEXT() CONTAINS()) for any number of arbitrary keywords using standard LinqToSql query syntax? I'd obviously like to avoid having to use a Stored Proc or have to generate a Dynamic SQL calls. Obviously I could just pump the search string in on a parameter to a SPR...

How to do hit-highlighting of results from a SQL Server full-text query

We have a web application that uses SQL Server 2008 as the database. Our users are able to do full-text searches on particular columns in the database. SQL Server's full-text functionality does not seem to provide support for hit highlighting. Do we need to build this ourselves or is there perhaps some libraries or knowledge around on ho...

How do I compare phrases for similarity?

When entering a question, stackoverflow presents you with a list of questions that it thinks likely to cover the same topic. I have seen similar features on other sites or in other programs, too (Help file systems, for example), but I've never programmed something like this myself. Now I'm curious to know what sort of algorithm one would...

Make SQL Server index small numbers

We're using SQL Server 2005 in a project. The users of the system have the ability to search some objects by using 'keywords'. The way we implement this is by creating a full-text catalog for the significant columns in each table that may contain these 'keywords' and then using CONTAINS to search for the keywords the user inputs in the s...