Getting Started with SQL Server 2005 Full Text Searching: Part 3 – Using SQL

OK, so you have this spiffy catalog, and you’ve populated it with full text searches for your favorite tables. Now you’d like to actually use those index from within your SQL. There are four new commands you can use in SQL to get to your data. All are used as part of the where clause, and have similar syntax but different results.

The first is the one you’ll probably use the most, it’s the contains command. You simply pass in the column name and what you want to search for.

select col1, col2 from myTable
where contains(fts_column, ‘searchword’)

For fts_column you can use the name of one of the columns that was indexed, or you can use * (and asterisk) to search in all of the columns that were full text search indexed. In the single quotes you put in the word or phrase you want to look for.

Contains searchs for an exact match. It either finds it or it doesn’t, and it has to be an independent word. For example, if your text field contained “I love the Mythbusters every week.” and you searched for ‘Mythbuster’, would NOT return a match.

If you want your text searching to be a little more open minded, use the freetext command instead. The syntax is identical to contains, including the ability to use an asterisk.

select col1, col2 from myTable
where freetext(fts_column, ‘searchword’)

In this case, however, a search of our afore mentioned text field for ‘Mythbuster’ would return a match, as freetext understands that Mythbuster and Mythbusters are essentially the same word.

In your application, you might consider using a check box that says “exact match”. For exact match queires use the contains keyword, when the user does not check you can use the freetext command.

It’s also possible to return a list of results that are sorted by a rank. The rank indicates the strength of the match to the search phrase passed in. To get a list of ranks, use either the containstable or freetexttable commands. Their syntax is like their cousins, as is the method it uses for searching (containstable is exact, freetexttable is more liberal). The only addition is the first parameter must be the name of the table, then comes the column name and search condition.

Instead of rows, what is returned are two columns: key and rank. The rank is a relative score from 0 to 1000 that indicates the strength of the match. A higher value means it’s a better match.

The key is the primary key from the table you’re searching. You can then use this key to pull back the data from the main table. Let’s do a simple example: you want an exact match for all employees who live in Alabama. Unfortunately the DBA who created the table had just come off a three day drinking binge, and instead of separate street / city / state fields, just created a big text field called emp_address.

select rank, emp_id, emp_name from empTable
join freetexttable(empTable, emp_address, ‘Alabama’) ftt
on empTable.emp_id = ftt.[KEY]

This would return something like:

Rank emp_id emp_name
255 12345 Jamie Hyneman
128 45678 Adam Savage

And there you go, four ways you can have your SQL leverage the power of full text searching to return results.


7 thoughts on “Getting Started with SQL Server 2005 Full Text Searching: Part 3 – Using SQL

  1. Hi,

    I have recently been browsing the web for articles on SQL 2005 full text searching and I must say that this is the best by far. For beginners this is a perfect example!


  2. its good to have like this …but it is very much appreciated if full text already created… now i try to backup my database ..
    size of the database is very small around 2 gb but the backup size seems to be 7 gb.

    Observed that it is due to Full text search catalog.
    Its in production, now i wnat to take the Full backup as well as transaction backup for every 3 hrs.

    Could you please guide me how to seperate the full text form the database.


  3. i don know what is full text searching but from this article now i learned how to create and execute.
    Thank you so much.

  4. I have searched for word “floral” and in database we have description which have word florals. But FREETEXT don’t fetch records with word “florals” even though “floral” and “florals” are identical.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s