>Hi,
>
>I have a generic "search" class that I am improving. Before a user can do an add to a table, it searches about 12 different ways and then presents the results to the user (to cut down on duplicates). I have always thought I should write an article on it as it has been quite effective at cutting down on dups.
>
>Revisiting it now and trying to improve one search. For background, this search works fine. I call it "first initial search".
>
>table contains: S. Stallone
>user enters: Sylvester Stallone
>
>class preps the search value by taking the first letter of the firstname and adds a period to do the search (lastname = "Stallone" and firstname = "S."). (of course, all searches case insensitive) Works fine.
>
>Where I am a bit stuck is trying to get an *optimized* search for the reverse of this:
>
>table contains: Scott Peck
>user enters: M. Scott Peck
>
>What I did (2001 vintage version): took just the first character of the firstname and searched for that (lastname = "Peck" and firstname = "M) - which actually does not catch the above.
>
>Reviewing the table though, I realize that most of the entries are like the above and so I need to change this.
>
>I could just strip the initial and do the search but I am wondering if there are other options - I have not used the LIKE operator in SQL. Is it optimized or can anyone suggest other fast ways to do this?
>
>Albert

we have a similar functionality and as a solution we create a separate column in the table (or you could create a separate lookup table) to save the search string. In that searchstring we save all possible combinations of names. In your case it would be:
^SPECK^MSPECK^MPECK^SMPECK^

When you do the search you do the same trick with the input name and for each combination name part you do a substring search in the lookup field:

SELECT * FROM Names Where "^MSPECK^" $ Searchstring

So either way you type it you should find the record.