Troubleshooting your FORMSOF & INFLECTIONAL searches not working, SQL Server 2005
Posted by Iain on Wed (9/01/08) at 1:08pm to Development
For the life of me, I couldn’t figure out why I didn’t get different results, using SQL Server 2005, for a simple Full-Text Search query and a Full-Text Search query using the FORMSOF and INFLECTIONAL keywords.
What I stumbled across eventually was that when creating and configuring my SQL Full-Text Indexes, I had left the language parameter as the default selection. When I changed this to English, voila!, my FORMSOF (INFLECTIONAL, @query) queries started to produces fantastic search results!
Also, an example query showing how to parameterize the the search term, and how to rank your results:
SET @query = ‘FORMSOF (INFLECTIONAL, ‘ + @query + ‘)’
SELECT FT.rank, P.id, P.title
FROM Products P
INNER JOIN CONTAINSTABLE (Products, *, @query) AS FT ON P.id = FT.[key]
If you’ve any questions, I’ll do my best to help. Also, if you’re just starting with Full-Text, check out my posts on installing Full-Text Search on SQL Server 2005 and how to setup and configure SQL Server 2005 Express Full-Text Search the easy way.
on January 9th, 2008 at 1:16pm
[…] For each column, select the column, and select the appropriate language To understand why the language parameter is important, see my post on use of the FORMSOF and INFLECTIONAL keywords in SQL Full-Text Search […]
on April 9th, 2008 at 9:21pm
Is there a table that can be viewed to show what the variations of inflectional words are? I need to do inflectional searches based upon a list of about 400 words, but I don’t know if all of the words I’m expecting to be found will be.
on January 15th, 2009 at 10:45am
Hi there,
I am having a tough time with these indices. I believe I have all right installation for full text. A query like select * from Item
where contains(title, ‘used and chair and pune’) returns the result I want, but a query like select * from Item
where contains(title, ‘((formsof(inflectional,used)) and (formsof(inflectional,chair)) and (formsof(inflectional,pune)))’)
does not return anything. So is this something to do with the way inflectional keyword works? Any comments appreciated.
on January 15th, 2009 at 11:13am
Hi Abhang, I’m can’t say for sure why you’re query isn’t working, but I see several differences between your proposed query and the example I provide.
Firstly, you’re using CONTAINS and not CONTAINSTABLE. If you use CONTAINSTABLE, note that its joined using INNERJOIN to the table you’re searching, and it’s parameters are the table name, column names, and then your query.
Finally, you’re applying FORMSOF to each query term individually - in my example I put the whole query together, and that works fine for me.
Good luck, let me know how you get on.
on January 22nd, 2009 at 7:41pm
Howdy,
Thanks dude. Actually the way I was building the query string was incorrect. I had too many ( and ) in the query. Just plain ‘formsof(inflectional,used) and formsof(inflectional,chair)’ works. I am planning to create a Thesaurus for improving the search quality, lets see how that goes.