maybe there is a simple answer to my problem:
I have a mssql table ('Person') with a field for first and one for last name.
I am coding a Program, where the User should be able to search for persons.
SELECT id, firstname, lastname FROM Person WHERE firstname LIKE
@searchstring + '%' OR lastname LIKE @searchstring + '%'
doesn't work very well, because if the user types 'John W' nothing is found.
For example the person I search for is called John Williams:
It should work with searchstrings like 'John W', 'Williams Jo', 'Will' or 'Joh'
Concatenating first and lastname and use %Searchstring% also doesn't work well, because if the searchstring is e.g. 'Will' it should find people who are called Will, Willibald, Williams or Willson or something like that, but not a Person called Goodwill e.g.
And the query should be as fast as possible, because it is executed at every keydown…
edit: forgot to mention that the database must not be changed, because it is from another company.
Thank you for your help.
Best Answer
There are two main components to the question: the searching of the name column and how the data is delivered to the user. As predictive typing is more related to programming, caching, etc. and as Aaron mentioned is not a good idea to do on each keystroke, I will not cover it.
As for how best to query the database:
The logical choice here is Full Text Search, because that provides the exact features you're looking for:
However, the OP has updated the question to say that the database belongs to another company. So that leaves two main options:
LIKE
operatorOption 1
To achieve this, you'd need to split the user's input by spaces (or some other desired delimiter) and dynamically build your
WHERE
clause based off that in a format likeWhile possible, this is far from ideal as the
LIKE
operator is not very efficient when it comes to comparing strings, even if there are only 200,000 rows. It will not scale well and I wouldn't trust it for this use case in a production system:Aside from this, you'll have to accommodate users who have special characters (language-specific characters, punctuation), extra spaces, abbreviations, etc. in their input.
Not the "right" choice by any means, but you can probably hack it together if you must.
Option 2
Use replication, mirroring, etc. to get a local copy of the user data and build a Full Text Index on it to leverage all of the features, like a thesaurus, language support, and limiting search results based on rank of relevance which would greatly help your predictive text typing if you still decide to pursue it.
While requiring more up front effort, this is the "right" way to do this.