SQL Server 2008 - Full Text Query

Question

Hello,

I have two tables in a SQL Server 2008 database in my company. The first table represents the products that my company sells. The second table contains the product manufacturer’s details. These tables are defined as follows:

  Product
  -------
  ID
  Name
  ManufacturerID
  Description

  Manufacturer
  ------------
  ID
  Name

As you can imagine, I want to make this as easy as possible for our customers to query this data. However, I’m having problems writing a forgiving, yet powerful search query. For instance, I’m anticipating people to search based on phonetical spellings. Because of this, the data may not match the exact data in my database. In addition, I think some individuals will search by manufacturer’s name first, but I want the matching product names to appear first. Based on these requirements, I’m currently working on the following query:

  select
    p.Name as 'ProductName',
    m.Name as 'Manufacturer',
    r.Rank as 'Rank'
  from
    Product p inner join Manufacturer m on p.ManufacturerID=m.ID
      inner join CONTAINSTABLE(Product, Name, @searchQuery) as r

Oddly, this query is throwing an error. However, I have no idea why. Squiggles appear to the right of the last parenthesis in management studio. The tool tip says "An expression of non-boolean type specified in a context where a condition is expected". I understand what this statement means. However, I guess I do not know how COntainsTable works. What am I doing wrong?

Thank you

Answer 1

First off I think you need an 'ON' clause when joining on the full-text CONTAINSTABLE. See this example from Microsofts website:

USE Northwind;
GO
SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK
FROM Categories AS FT_TBL 
    INNER JOIN CONTAINSTABLE (Categories, Description, 
        '("sweet and savory" NEAR sauces) OR
        ("sweet and savory" NEAR candies)'
        ) AS KEY_TBL
        ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
    AND FT_TBL.CategoryName <> 'Seafood'
ORDER BY KEY_TBL.RANK DESC;
GO

http://msdn.microsoft.com/en-us/library/ms177652.aspx

You need to link the Product table to the correct search results by adding a join clause like this:

SELECT
    p.Name AS 'ProductName',
    m.Name AS 'Manufacturer',
    r.Rank AS 'Rank'
  FROM
    Product p 
      INNER JOIN Manufacturer m ON p.ManufacturerID=m.ID
      INNER JOIN CONTAINSTABLE(Product, Name, @searchQuery) AS r
        ON p.ID = r.[KEY]

Otherwise you don't known which rows in the results table join to the appropriate rows in the source tables.

Secondly, CONTAINS and CONTAINSTABLE both return exact string matches unless you are using wildcards ( e.g. '"bol*"' ). Even when using wildcards you can only use suffix wildcards, so for '"bol*"' it will find all words that start with 'bol'. '"*bol*"' will not find words with 'bol' in them. For a not-exact, fuzzy style search you should be using FREETEXT( ... ) or FREETEXTTABLE( ... ).

Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string.

http://msdn.microsoft.com/en-us/library/ms177652.aspx

The trade off is that CONTAINS performs a lot better than FREETEXT, and FREETEXT provides more natural results.

Lastly, if you want to match phonetically SQL Server has the built in function SOUNDEX which tries to generate an alphanumerickey based on the phonetic spelling or the argument.

-- Using SOUNDEX
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');

    Copy
----- ----- 
S530  S530  

(1 row(s) affected)

http://msdn.microsoft.com/en-us/library/ms187384.aspx

I would advise against using soundex though, I have found it limiting in the past because Soundex really only ensures that to words start off phonetically. Soundex always returns the starting letter and the first three consonant sounds represented numerically. There are better versions of the algorithm out there that were designed as a replacement for soundex, see Double Metaphone (or a T-SQL version of Double Metaphone that you can use as a scalar function).

Soundex or DoubleMetaphone are not built into SQL Server Fulltext so you would need to implement the search manually.

SELECT * FROM MyTable where SOUNDEX( MyColumn ) = SOUNDEX( 'MySearchQuery' )