locked
Best way to perform a find & insert RRS feed

  • Question

  • My question seems simple enough. We have a database running in msSQL 2005 and I need to perform a find and insert. Basically, we have a keyword field that I want to find a certain instance of a keyword and, when found, add a string of relevant words/phrases to improve our search function.

    What are the steps to do this in msSQL 2005.

    Thanks for the assistance.
    Friday, January 8, 2010 4:04 PM

Answers

  • so lets suppose you discover the keyword "heart attack" and you want to insert mycardial infarction, heart attack and cardia arrest into the keyword list field for this row. There is no good way of doing this. Its best implement by the thesaurus option.

    What you will need to do is build a taxonomy or list of keywords and then update the keyword field for a row with this taxonomy list on the fly.

    Inverted file indexes are helpful for this.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by KJian_ Monday, January 25, 2010 9:32 AM
    Friday, January 8, 2010 5:08 PM
  • SQL Server does not provide a thesaurus that would match your needs.  It provides a mechanism for you to make your own thesaurus, inserting the related expresssions that matter to you.  If you already have a related list, similar to what a phone book might offer (E.g SEE ALSO:  photography, lithography) then use it to get started.

    If you found a complete English language thesaurus and used that for your thesaurus, you would see that it would cause more harm than help.  Some words have so many meanings that they would appear in many different searches, which is not useful.

    A targeted thesaurus, though, will hit the things you are interested in.  In addition, of course, you can expand the thesaurus as you find other terms that matter to you.

    RLF
    • Marked as answer by KJian_ Monday, January 25, 2010 9:32 AM
    Monday, January 11, 2010 12:13 AM

All replies

  • so lets suppose you discover the keyword "heart attack" and you want to insert mycardial infarction, heart attack and cardia arrest into the keyword list field for this row. There is no good way of doing this. Its best implement by the thesaurus option.

    What you will need to do is build a taxonomy or list of keywords and then update the keyword field for a row with this taxonomy list on the fly.

    Inverted file indexes are helpful for this.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by KJian_ Monday, January 25, 2010 9:32 AM
    Friday, January 8, 2010 5:08 PM
  • I somewhat understand the thesaurus option but am curious as to how detailed it is and how it compares or compliments the inflectional set-up.

    To put it into our real-world application. Our keyword strings have the keyword "taxicabs" in them for those type of businesses. Would the thesaurus function also find "cabs, taxi service, etc.?"

    Is there a place where we can look to see msSQL's thesaurus or inflectional resource where we can see if certain words are included by default. (example. Chiropractic vs. Chiropractor). 

    Thanks.
    Saturday, January 9, 2010 11:13 PM
  • SQL Server does not provide a thesaurus that would match your needs.  It provides a mechanism for you to make your own thesaurus, inserting the related expresssions that matter to you.  If you already have a related list, similar to what a phone book might offer (E.g SEE ALSO:  photography, lithography) then use it to get started.

    If you found a complete English language thesaurus and used that for your thesaurus, you would see that it would cause more harm than help.  Some words have so many meanings that they would appear in many different searches, which is not useful.

    A targeted thesaurus, though, will hit the things you are interested in.  In addition, of course, you can expand the thesaurus as you find other terms that matter to you.

    RLF
    • Marked as answer by KJian_ Monday, January 25, 2010 9:32 AM
    Monday, January 11, 2010 12:13 AM
  • Russell is completely correct here - you need to build your own thesaurus.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, January 11, 2010 3:26 PM
  • Hello Russel or Hilary,

    Apparently our programmer doesn't have the depth of skill necessary to make this function properly. Would either of you have an interest in taking a look at our set-up to see if you can solve and implement the best way to accomplish what we need done? I can give you our remote desktop access information.

    I think I understand the thesaurus set-up. My only question would be the steps to edit and add to the thesaurus. It would be my privilege of going in and adding words which I am completely fine with.

    Regards,

    Adam
    Wednesday, January 13, 2010 6:16 AM
  • Yes, edit the thesaurus file that corresponds to the language you are running. Use notepad as your editor.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Wednesday, January 13, 2010 1:45 PM