I am making a quiz system, and when quizmakers insert questions into the Question Bank, I am to check the DB for duplicate / very highly similar questions. Testing MySQL's MATCH() ... AGAINST(), the highest relevance I get is 30+, when I test against a 100% similar string. So what exactly is the relevance? To quote the manual: Rele...
I installed SQL Server 2008 Express with Advanced Services, but when I try to create a new database, the option Full-Text indexing is greyed out, I believe the full-text indexing has been installed, because I did a query as below: use [mydbname] select fulltextserviceproperty('isfulltextinstalled') This query returns 1, so I think it...
How do I sort the query results by keyword occurrence in column? I'm using SQL Server 2008 with Full-text indexing. ...
I'm storing papers in SQL Server 2005 and am looking for a way to paste in the text of a paper and then search for potential plagiarism (copied content) in the database. What's the best way to go about this? Is there a way to get a gauge for the extent to which something is similar to something else using full-text indexing, for several...
I'm using a contains predicate to find phrases in a SQL Server indexed text field. Is there a way to return the portion of the text field that contains the searched phrase, or some area around it? For example, if I'm searching for "all men are created equal" in the Gettysburg address (excerpted below), I'd like to return "dedicated to ...
I created a full-text index on a text column and subsequent contains queries return results that don't include the search term. An example query is: SELECT TOP 10 serial_no,writer_id,myField,assignment_type,subdate,title FROM writing WHERE contains(myField,'"take it once"'); ...
I've created a content-managed website using ASP.NET MVC. All content is stored in the 'Content' table in a SQL Server database, with the text itself stored in a column of datatype 'XML'. I want to add a search feature to the site that will search all the XML content and return a list of results, each of which links to the content item....
How do I list all tables / columns in my database that have a full-text index? ...
I'd like to implement fulltext search for a datatable ? are there any .net libraries which support indexing/searching on the fly ? ...
Hi, I have a set of 4 tables that I want to search across, each has a full text index, and here is my query; SELECT categories.name AS category, categories.id AS category_id, host_types.name AS host_type, host_types.id AS host_type_id, hosts.name AS host, hosts.id AS host_id, products.name as name, products.id AS ...
I will be delivering a set of static HTML pages on CD-Rom; these pages need to be fully viewable with no Internet access whatsoever. I'd like to provide a full-text search (Lucene-like) for the content of those pages, which should "just work" from the CD-Rom with no software installation on the client machine. A search engine implement...
By default, when one tells SQL Server (currently using 2008) to Full-Text index a column, it treats characters such as "@" and "." as work-breakers, similarly to " ". I'd like to restrict the work-breaking characters to just be " ", so that "[email protected]" is treated as a single word. It appears that one can choose a "Langua...
I want to compile a list of gotchas when using SQL Server full-text search in all versions. For instance, Here's a SQL Server Full-Text gotcha: SQL Server 2008 R2 has a new feature called Data-Tier Applications - which is a way to package a database schema meant to be deployed elsewhere. Certain objects like Full-Text Ca...
I've got a simple select query which executes in under 1 second normally, but when I add in a contains(column, 'text') into the where clause, suddenly it's running for 20 seconds up to a minute. The table it's selecting from has around 208k rows. Any ideas what would cause this query to run so slow with just the addition of the contains...
Hi, I'm trying to make a detailed search with asp and SQL Server Full-text. When a keyword submitted, I need to search in multiple tables. For example, Table - Members member_id contact_name Table - Education member_id school_name My query; select mem.member_id, mem.contact_name, edu.member_id, edu.school_name from Members me...
Hi, I have a question about scheduling a rebuild and reorganize of a SQL Server 2008 Full-Text Catalog. When I go to the catalog's Properties then Population Schedule, the wizard seems pretty straight forward except for I don't see a way to specify that I want a rebuild rather than a reorganize. I wanted to schedule a reorganize to be...
Hi, we have a table with +- 500k rows in Sql Server 2005 database and one of its columns has a full-text index. We were doing some tests and found that SELECT TOP 1 ... WHERE CONTAINS(fullTextColumn, 'anyValue') was taking more than two minutes to return. However the same select, but with TOP 2 instead of 1 return in a few seconds. A...
If you want the relevance and also the results to be sorted by relevance the common format of a FULLTEXT query is: SELECT name, MATCH(name) AGAINST('Bob') AS relevance FROM users WHERE MATCH(name) AGAINST('Bob') As a developer I always like to make my code DRY(don't repeat yourself). Is there any reason not to write the query as: SEL...
I'm designing an internal web application that uses MySQL as its backend database. The integrity of the data is crucial, so I am using the innoDB engine for its foreign key constraint features. I want to do a full-text search of one type of records, and that is not supported natively with innoDB tables. I'm not willing to move to MyISA...
Full-Text Search for category with all parents in a row: CatLevel1 >> CatL2 >> CatLn >> SearchedCategory I've got 4 columns in my category table: CatID, CatName, CatDepth, ParentID Some categories got a 3 level depth and some other 7. I want to have the target category with all its parents in one row. Let me explain by an example: Se...