freetext

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...

Slow SQL Query due to inner and left join?

Can anyone explain this behavior or how to get around it? If you execute this query: select * from TblA left join freetexttable ( TblB, *, 'query' ) on TblA.ID = [Key] inner join DifferentDbCatalog.dbo.TblC on TblA.ID = TblC.TblAID It will be very very very slow. If you change that query to use two inner joins instead of a left joi...

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...

Why or How does FREETEXTTABLE give a rank value higher then others.

There is a store procedure that uses FREETEXTTABLE twice on two tables and then merges the results and returns the top 50. The problem is if I do a search on "Women of Brewster", the results returns "Confession of an ex doofus motha" with a rank of 143 from table A and second "Women of Brewster Place" with a rank of 102 from table B. ...

How can I search for special characters using Freetext in SQL Server

I have a web page that allows users to search a Question & Answer database table (no its not StackOverflow). The web page accepts the search term from the user and passes it to a stored procedure as a parameter on a SQL Server 2005 database. That sproc does a SELECT on the table in question, using FREETEXT in the WHERE clause to search...

Slow inital query when using FreeTextTable in SQL Server 2005

The following FreeTextTable query takes > 10 seconds on the first request, but about a second for subsequent requests: SELECT [Key], [Rank] INTO #matches FROM FREETEXTTABLE(Book, [Description], @searchWord) After approx. 10 minutes of inactivity the next request will once again take > 10 seconds. This seems like the freetext cache is ...

SQL Server "Optional" FreeText Search

I'm writing a search query in SQL Server 2005 for a discussion board. The proc takes several parameters, but most are "optional". There's one search field for Message Body, which I have a full text index on. Here's the problem.. If I pass in a value to search against with FreeText, the search works fine (thank you Microsoft). Howeve...

Fulltext search (sql server 2005) works only on some fields..

OK this is the situation.. I am enabling fulltext search on a table but it only works on some fields.. CREATE FULLTEXT CATALOG [defaultcatalog] CREATE UNIQUE INDEX ui_staticid on static(id) CREATE FULLTEXT INDEX ON static(title_gr LANGUAGE 19,title_en,description_gr LANGUAGE 19,description_en) KEY INDEX staticid ON [defaultcatalog] WIT...

Override IE email auto formatting.

Hello, Our site makes use of a web based, rich-text editor FreeTextBox. In IE, not FF, if a user types in something like: someone@blah IE automatically creates a mailto hyperlink. I have tested this with other text editors out there and the story is the same with all of them. So the question can I override this browser behavior s...

Python: convert free text to date

Hi, Assuming the text is typed at the same time in the same (Israeli) timezone, The following free text lines are equivalent: Wed Sep 9 16:26:57 IDT 2009 2009-09-09 16:26:57 16:26:57 September 9th, 16:26:57 Is there a python module that would convert all these text-dates to an (identical) datetime.datetime instance? I would like to...

What's your solution for free-text search and sort ?

AFAIK,MySQL performs really bad at this, what's your solution? BTW,what's the solution of SO? EDIT Please pay attention that free-text search itself is pretty fast in MySQL, but not the case when the result also needs to be sorted on an attribute! ...

FreeText COUNT query on multiple tables is super slow

I have two tables: **Product** ID Name SKU **Brand** ID Name Product table has about 120K records Brand table has 30K records I need to find count of all the products with name and brand matching a specific keyword. I use freetext 'contains' like this: SELECT count(*) FROM Product inner join Brand on Product.B...

Sql Server 2005 Fulltext case sensitivity problem

I seem to have a weird bug in Microsoft SQL Server 2005 where FREETEXT() searches are somewhat case-sensitive despite the collation being case-insensitive (Latin1_General_CI_AS). First of, LIKE queries are perfectly case-insensitive, so WHERE column LIKE '%word%' and WHERE column LIKE '%Word%' return the same results. Also, FREE...

SQL Server Freetext and Contains functions at Godaddy webhosting

I have a webhosting at Godaddy with MS-SQL, I have a table called Product with 3 columns (Id, Title, Details) I want to implement Freetext or Contains function to search multiple words. When I execute the query: Select * from Product where freetext(Title,'Car Honda') It answers me "Cannot use a CONTAINS or FREETEXT predicate on table...

How to handle single character search terms in MS-SQL FreeText searching?

I am having a problem with a FreeText search, currently running on a SQL 2000 server. In a table of approximately 1.3 million rows which contain company names, I am attempting to use a FreeText query. However since SQL Server strips out special characters and single characters when building its index, our code does the same when subm...

FreeText Query is slow - includes TOP and Order By

The Product table has 700K records in it. The query: SELECT TOP 1 ID, Name FROM Product WHERE contains(Name, '"White Dress"') ORDER BY DateMadeNew desc takes about 1 minute to run. There is an non-clustered index on DateMadeNew and FreeText index on Name. If I remove TOP 1 or Order By - it takes less then 1 second to run...

SQL Server 2005 FREETEXT() Perfomance Issue

I have a query with about 6-7 joined tables and a FREETEXT() predicate on 6 columns of the base table in the where. Now, this query worked fine (in under 2 seconds) for the last year and practically remained unchanged (i tried old versions and the problem persists) So today, all of a sudden, the same query takes around 1-1.5 minutes. ...

Google like search query using Full Text Search in SQL Server

I am running a small website wherein I would like to make a functionality like Related Links section. For that I created fulltext catalog and index. So far I tried many ways to create search query which behaves like google, but I would say i was not even 10% close to what google is doing. During my research what i found was only follo...

Scalable freetext & parametric searching (C#)

Hey, Currently, we've got an application that needs to perform very fast searches on ~2 mill records. Searches need to search both a large free-text field, and a number of integer/decimal fields between different ranges, along with various functions/computations & sorting. Currently, we're handling this with a big MSSQL database, usin...

Full-text Search on documents and related data mssql

Currently in the middle of building a knowledge base app and am a bit unsure on the best way to store and index the document information. The user uploads the document and when doing so selects a number of options from dropdown lists (such as category,topic,area..., note these are not all mandatory) they also enter some keywords and a d...