The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

Full Text Search Rank


The example is like the following:

I have in AlgoData table the following xmls containning the following strings inside of them:

Dogs: My dog is really terrified of everything., pack leader, loud …
I love my dog
How can I take care of my dog’s seperation anxiety?
Should I fire my dog walker? | Ask Metafilter
My dog is afraid of the outdoors – Pets Forum – GardenWeb
my dog
Raise your kid like a dog (?!)
My dog is dog aggressive What Can I do about this aggression
Creampuff Revolution: Creampuff to Dog: You Smell Like Gym Socks
would like to take my dog from the uk to tenerife by ferry …
Are Friendly Dogs Welcomed? – Santa Fe Scoop
tBlog – I Had My Dog Euthanized Yesterday
Dogs: My dog is really terrified of everything., pack leader, loud …
FIGHTING DOGS – SPEAK OUT AGAINST THE BREED BAN IN GERMANY!
Selection Quiz for Choosing a Dog
Pets on my Mind – Commercial Appeal Blogs
Baby Steps: More Belly-Achin’ and Dog Love
Hoover SteamVac Carpet/Upholstery Detergent – washes that dog …
Dog Illness And Treatments (Pets: Dog Health)

I needed to develop a script that will calculate the ranking like the follows:

DECLARE @topRank int

set @topRank=(SELECT MAX(RANK) FROM
freetexttable( AlgoData, AlgoData,‘I would like to take my dog out’,1))

SELECT
ftt.RANK,
(CAST(ftt.RANK as DECIMAL)/@topRank) as matchpercent,
AlgoData.AlgoDataID,
AlgoData.xmlfilename
FROM  AlgoData
INNER JOIN
FREETEXTTABLE( AlgoData, AlgoData, ‘I would like to take my dog out’) as ftt
ON
ftt.[KEY]= AlgoData.AlgoDataID
ORDER BY ftt.RANK DESC

Small explanation:

The table these functions return contains two columns:

  1. KEY: this contains the primary key of the row in the indexed table. Its data type is the same as the primary key in that table.
  2. RANK: this is a positive integer representing the relative strength of the match to your search term. This number is not an absolute value—it does not start at some fixed value and work down to near zero. The specific value is only relative compared to other results from that Free-Text query. Comparing the RANK value across different Full-Text queries does not necessarily get meaningful results.

Now, I have made just INNER JOIN the table returned from the Free-Text function on the KEY field to our desired results, then sort by the RANK column for sorting effects.

For a match percentage, I needed to divide the RANK by the top RANK value. It cannot be done unfortunately, with just one Free-Text query; it requires selecting the TOP value then selecting the desired results and creating a calculated field for the percentages.

Percentage matches (As sometimes you can see on some site search engines):

f

Now, I will go back and remind you I have looked for: ‘I would like to take my dog out’

And I have inserted the following xml in db already:

Dogs: My dog is really terrified of everything., pack leader, loud …
I love my dog
How can I take care of my dog’s seperation anxiety?
Should I fire my dog walker? | Ask Metafilter
My dog is afraid of the outdoors – Pets Forum – GardenWeb
my dog
Raise your kid like a dog (?!)
My dog is dog aggressive What Can I do about this aggression
Creampuff Revolution: Creampuff to Dog: You Smell Like Gym Socks
would like to take my dog from the uk to tenerife by ferry …
Are Friendly Dogs Welcomed? – Santa Fe Scoop
tBlog – I Had My Dog Euthanized Yesterday
Dogs: My dog is really terrified of everything. Pack leader, loud …
FIGHTING DOGS – SPEAK OUT AGAINST THE BREED BAN IN GERMANY!
Selection Quiz for Choosing a Dog
Pets on my Mind – Commercial Appeal Blogs
Baby Steps: More Belly-Achin’ and Dog Love
Hoover SteamVac Carpet/Upholstery Detergent – washes that dog …
Dog Illness And Treatments (Pets: Dog Health)

As you can see above, the query returned RANK results under the RANK column above and I have made calculation of the match percent under the matchpercent column above.  In fact these are the strings that returned high rank with in this search query: “I love to take my dog out”

  1. “My dog is dog aggressive What Can I do about this aggression”
  2. “Dogs: My dog is really terrified of everything., pack leader, loud …”
  3. “Dog Illness And Treatments (Pets: Dog Health)”

As you can see above, the full-text search engine returned RANK 178 with percent of 0.79 to the first 3 and the others got RANK 126 with percent of 0.56.

All the rest returned lower percent value as you can see.

** The RANK calculation is not something I can control, and calculation is done via the link I gave you above.

Conclusion:

It depends what is the occurrence you want for your results. If you don’t need them to be that accurate may be you can consider using it.

Advertisements

June 23, 2009 - Posted by | full text search | ,

No comments yet.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: