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 to use FREETEXTTABLE with the table that has composite primary key? E.G. Table(FirstID int, SecondID int, Text ntext) Primary Key(FirstID, SecondID) FREETEXTTABLE returns something that is similar to (or maybe is a) GUID and Rank. How to join this "rank" table with the original one? ...
Hi there, I'm trying to fix a bug with a site search function and have isolated it down to an issue with the FREETEXTTABLE function. I have a the following query: SELECT * FROM dbo.SiteContentForSearch INNER JOIN FREETEXTTABLE(SiteContentForSearch, sSearchText, 'NFC' ) AS SearchResultTable ON dbo.SiteContentForSearch.liSearchID = ...
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 ...
I have a query using a FREETEXTTABLE full text search which works perfectly for every column included in the index except for the primary key. The primary keys are in a format like abcdef123456 and when you search for abcdef123456 you get that one record returned. However, if you search for abcd or 12345 you get no results (unless that...
I am joining onto a freetexttable using a searchTerm parameter which is also used in a number of other freetexttables in the query. I would like to remove the city name from this parameter if it exists in the current row. Trying to use replace like this: freetexttable(Gigs, Name, REPLACE(@searchText, c.CityName, '')) gigkt ON g.GigID ...
I'm using MSSQL's full text indexing on a handful of tables in my CMS and am unfortunately stuck with SQL Server 2000. I'm querying the index using freetexttable joins and am having pretty good results, but we have some unique terms that are likely search queries that do not appear to be stemming. For example a query using the term "sm...
I had a working FREETEXTTABLE query that searched for a @searchString. I now need to UNION that with another simple query that tries to parse the @searchString into an INT, and if it succeeds, filtering the table by looking for the row with PK equal to the parse @searchString. Previously, I could easily JOIN the FREETEXTTABLE result to ...
I tried two different variations on the same thing. The first version selects from freetexttable, the other insets into a temp table and selects from that. I've tried numerous variations on the first version (select several combinations, at both levels of scope, of group by, distinct, and casting [rank] to an integer. Regardless, the ...
Hi I have a table (lets say it has one column called 'colLanguage') that contains a list of skills and has a full text index defined on it. One of the entries in the table is 'c#' but when I search for 'c#' (using the following SQL) I get no results back. select * from FREETEXTTABLE(tblList, colLanguage, 'c#') Can anyone help? Thanks...
I'm using SQLServer 2008 and if I perform the following query: SELECT * FROM FREETEXTTABLE(SomeTable, Name, 'a name that I know exists') I get the rows back that I would expect, but the rank is always 0. Searching for a solution to this problem, I found this question on the Microsoft ASP.NET forum, and sure enough if I add: ALT...
MS SQL Server 2005: table1 has a full text index. I want to run multiple freetexttable searches against it in a single query, but the two attempts i have fail. any help would be appreciated, thanks! p.s. am willing to upgrade to sql 2008 if it fixes this :) CREATE FUNCTION fnt_FullTextSearch ( @s NVARCHAR(4000) ) RETURNS TABLE AS ...
I'm using the SQL Full-Text Search and have a stored proceedure that uses the FREETEXTTABLE function. This all works great, however, I have noticed that if I search for something such as 'Chapter 19' the 19 seems as if it is thrown away and the search only searches on 'Chapter'. Also if I search for just '19' I get no results. I know ...