AdobeStock_455007340

Performing SQL Server FREETEXT Searches

FREETEXT provides a simple mechanism by which to perform SQL Server 2005 full-text searches, matching by meaning as opposed to exact text match. Here is a simple example:
SELECT *
FROM my_table
WHERE FREETEXT(column1, 'rabbit food');

FREETEXT(column1, ‘rabbit food’) means perform a FREETEXT lookup on column column1 looking for anything that could mean rabbit food (but not necessarily those two exact words, and not necessarily as a phrase).
You can also search across all columns indexed for full-text search by using FREETEXT(*, ‘search text’).
If double quotes surround a search term then that exact phrase is matched, not the meaning.

5 responses to “Performing SQL Server FREETEXT Searches”

  1. todd sharp Avatar
    todd sharp

    Excellent posts Ben, very helpful. Thanks.
    One question, I’m assuming the full text searching is disabled in the Express version? Do you know for sure?

  2. Ben Forta Avatar
    Ben Forta

    Todd, I believe that that is correct. See http://msdn.microsoft.com/vstudio/express/support/faq/#sql
    — Ben

  3. todd sharp Avatar
    todd sharp

    Good link. Q 51 states:
    SQL Server 2005 Express Edition with Advanced Services is a download that includes the SQL Server 2005 database engine, SQL Server 2005 Reporting Services, SQL Server 2005 Management Studio Express and Full-Text Search. SQL Server 2005 Management Studio Express will also be available separately as a download.
    So it looks like full text is available (in the Advanced Services download).

  4. Ben Forta Avatar
    Ben Forta

    Yep, I guess so! 🙂
    BTW, Oracle 10g Express (which is amazing, I’ll be blogging details soon) includes Oracle fulltext support.
    I love this new trend of releasing Express versions, be it Oracle Express, SQL Server Express, or the really great VIsual Studio Express implementations.
    — Ben

  5. Stephen Cassady Avatar
    Stephen Cassady

    Comparing the version limitations between Oracle, Microsoft, and IBM – the DB2 express level product (when I last checked) had the least amount of limitations from file size and processor limitation. In any case, I’m hoping in a year well see all three continue to evolve their express versions and create really substantial, free, entry level db products. Indeed, it’s the DB2 entry version that got me going "hummm, maybe time to look at DB2".

Leave a Reply