<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Bill Nicolich
»
Owning the Spelling Suggestion Feature
16 posts, Page 1 of 2
1
2
»»
Owning the Spelling Suggestion Feature
Rate Topic
Display Mode
Topic Options
Author
Message
Bill Nicolich
Bill Nicolich
Posted Saturday, July 19, 2008 11:12 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, April 09, 2013 1:17 PM
Points: 111,
Visits: 534
Comments posted to this topic are about the item
Owning the Spelling Suggestion Feature
Bill Nicolich:
www.SQLFave.com
.
Daily tweet of what's new and interesting:
AppendNow
Post #537168
tcnolan
tcnolan
Posted Monday, July 21, 2008 1:30 AM
Old Hand
Group: General Forum Members
Last Login: Wednesday, April 07, 2010 2:37 PM
Points: 315,
Visits: 164
Not a single reference to SOUNDEX? That doesn't seem right in a spelling suggestion article.
Post #537451
oldgoat
oldgoat
Posted Monday, July 21, 2008 1:44 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, March 28, 2012 8:30 AM
Points: 148,
Visits: 48
So add it!
SOUNDEX doesn't cover, particularly, the typos angle that Bill seems to have concentrated on. Probably a lot better for the search results, given its sometimes 'strange' output.
Seriously though, if you incorporated this with some Ajax, and used that for dynamic spelling correction, like Word does, it would be very nifty.
I recently built an Ajax menu that works on the very simple concept of 'update as you type'. Given folks' ability for typos and fat-finger, this might just get added to second-guess them.
Post #537457
Andy Warren
Andy Warren
Posted Monday, July 21, 2008 5:52 AM
SSCertifiable
Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462,
Visits: 1,384
My concern would be the number of round trips that might get generated. Other than that, I've always used a 3rd party spell checker (including the one in Word at times) and while they weren't cheap, they typically aren't hugely expensive either.
Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter
Post #537591
Mike Dougherty
Mike Dougherty
Posted Monday, July 21, 2008 7:04 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110,
Visits: 952
We went with the premise that people would rather find what they're looking for than be prompted with spelling corrections, so we wrote the search feature to join the product keywords to the misspellings as synonyms of the correct term, and match on whatever the customer entered. We monitor the misses between requested keywords and those already in the search table, so new misspellings are added from those seen in the wild. The programmatic permutations could be easily incorporated.
BTW, an added benefit of the "synonyms" notion is that we can make the plural form of the keyword a synonym of the singular so the content editors need only associate the singular keyword with the product. Also those keywords that are similar in concept can be aliased together so only one of that concept group needs to be applied to the product to allow any of the group to match. (different rank/weightings can be used to resolve exact matches higher in the results than alias matches)
Post #537635
GeorgeCopeland
GeorgeCopeland
Posted Monday, July 21, 2008 8:49 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 20, 2013 10:59 AM
Points: 129,
Visits: 224
I think a hash table would be a much better implementation. It would be hugely faster, and if the word doesn't hash, it would be easy to grab all the words near the hash as suggestions.
Post #537706
Mike C
Mike C
Posted Monday, July 21, 2008 8:51 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276,
Visits: 1,112
tnolan (7/21/2008)
Not a single reference to SOUNDEX? That doesn't seem right in a spelling suggestion article.
Soundex is horrible. And the SQL Server implementation doesn't match with the NARA Soundex standard anyway. If you want to do phonetic matching, I'd recommend starting with a better phonetic match algorithm. For spell-checking you'll get better accuracy using LCS, edit distance, or n-gram matching. Set-based operations work well with n-gram matching.
Post #537708
Mike C
Mike C
Posted Monday, July 21, 2008 8:53 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276,
Visits: 1,112
gcopeland (7/21/2008)
I think a hash table would be a much better implementation. It would be hugely faster, and if the word doesn't hash, it would be easy to grab all the words near the hash as suggestions.
A ternary search tree might work even better (although it would be hard to develop in SQL without using CLR). In the TST words that are similar tend to "bunch" together as well. BTW, just wondering but what type of hash function would you use to get similar words to group together? If the function is too simple you're going to pay a penalty in access costs.
Post #537712
Bill Nicolich
Bill Nicolich
Posted Monday, July 21, 2008 4:32 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, April 09, 2013 1:17 PM
Points: 111,
Visits: 534
Regarding number of round trips... In my implementation of spell suggestion, I use a single stored procedure call which uses a single T-SQL statement using a set-based approach even though the search entry is often multiple words. The search entry goes in, empty or filled spelling suggestion message goes out.
Although the performance seems great, I'd like to take the chance later to go into the specifics of the stored procedure including the performance angle and see if Andy and some of the other performance gurus around here will share their optimization tips.
Thank you everybody for taking the time to read. Let me know if you find other helper table techniques!
Bill
Bill Nicolich:
www.SQLFave.com
.
Daily tweet of what's new and interesting:
AppendNow
Post #538057
tcnolan
tcnolan
Posted Monday, July 21, 2008 5:41 PM
Old Hand
Group: General Forum Members
Last Login: Wednesday, April 07, 2010 2:37 PM
Points: 315,
Visits: 164
Sorry guys, my soundex suggestion was not meant entirely seriously. The typo function provided here is pretty well done, but I think adding phonetic search would add to its overall use. I also do not like SQL SOUNDEX very much. ;) http://microsoft.apress.com/index.php?id=72 or even a conversion of http://everything2.com/node/459981 could easily be worked in to the code here for phonetic search.
Post #538070
« Prev Topic
|
Next Topic »
16 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
