From here http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html One solution to find a word with a dashes or hyphens in is to use FULL TEXT SEARCH IN BOOLEAN MODE, and to enclose the word with the hyphen / dash in double quotes. Or from here http://bugs.mysql.com/bug.php?id=2095 There is another workaround. It was recently added to the manual: ” Modify a … Read more
LIKE is not fulltext search. In Fulltext search, MATCH(…) AGAINST(…) returns a matching score that can be roughly approximated as relevancy.
Unfortunately, doing this in PHP is prohibitively expensive (high CPU and memory utilization.) However, you can certainly apply the algorithm to small data sets. To specifically expand on how you can create a server meltdown: couple of built-in PHP functions will determine “distance” between strings: levenshtein and similar_text. Dummy data: (pretend they’re news headlines) $titles … Read more
unfortunately CONTAINS only supports prefix wildcards: CONTAINS(*, ‘”book*”‘)
Using FREETEXTTABLE, you just need to design some algorithm to calculate the merged rank on each joined table result. The example below skews the result towards hits from the book table. SELECT b.Name, a.Name, bkt.[Rank] + akt.[Rank]/2 AS [Rank] FROM Book b INNER JOIN Author a ON b.AuthorID = a.AuthorID INNER JOIN FREETEXTTABLE(Book, Name, @criteria) … Read more
You can’t do this through the wizard, because there are a couple of bugs. Even if I chose not to track changes and not to populate the index when it was finished, I still got an error about SQL Agent: Cannot load ‘Select or Create Population Schedules’ Full-Text Wizard form. SQL Server Agent is not … Read more
I found the answer to this today when converting my own database from SQL 2005 to SQL 2008. Pass “” for your search term and change the @search_term = ” test to be @search_term = ‘””‘ SQL server will ignore the double quotes and not throw an error. For example, the following would actually returns … Read more
Try, SELECT title FROM movies WHERE to_tsvector(title) @@ to_tsquery(‘squire:*’) This works on PostgreSQL 8.4+
I’m a fan of the Find-In-Files dialog in Notepad++. Bonus: It’s free.
This is unfortunately a weird exception to the use of query parameters (edit: but apparently not in the most recent point-release of each MySQL branch, see below). The pattern in AGAINST() must be a constant string, not a query parameter. Unlike other constant strings in SQL queries, you cannot use a query parameter here, simply … Read more