May 20, 2005 at 11:17 am
Hello,
You have received many good suggestions, but may I make another: If you would like searches to ignore punctuation, you may want to eliminate the punctuation from both the title and the search terms.
So if the title is "It's Cool!" for example, I might search for "its cool" or "it's cool" or "its cool!" and still find the book.
One way to do this would be to create a search table that contains a pre-sifted copy of the book title, and a key that relates back to the row for the book(s) that match. One could make this a separate table (for speed) or add a column to the existing table (for size). You need to add a "copy" of the original title because I assume that you want to preserve it WITH the punctuation.
The idea would be to replace all of the extraneous characters with nothings so they do not appear in the match target string. Then when the client eneters a search string, you apply the same replacement method to his search and *bingo* you've got an easier job for the match. You'd want to remove leading and trailing spaces, as well as converting double or triple .... back to a single space.
Now, if you want to make things really fast, you can put a checksum next to the sifted title (computed from the title) and search the checksum first. It is much faster to search an indexed checksum (an integer) than to use the like operator on a character column.
Experiment and look up the CHECKSUM() and REPLACE() functions in BOL. You should find some useful information there.
hth
JG
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply