Surname and first name search, matching accuracy order

Question

I am trying to create a simple search on my website (this is a social network site), and I want to search for all users registered on the website and return a list of offers (why this request is needed, a list of offers).

There are columns in my table that I want to find: userID , fname , lname .

I want to search the last 2 columns (2 names) and return them in order of how many names match the search query.

The first part of the query that I would run would be the following:

 SELECT userID, fname, lname FROM names WHERE 

but otherwise I need help.

EDIT: using this query to search:

 SELECT userID, fname, lname FROM names WHERE fname LIKE '%".$term."%' OR lname LIKE '%".$term."%' 

How can I order it so that the results are sorted by those that match at the beginning, so when I type M , it returns say, Mark to adam

Answer 1

You can try

 SELECT userID, fname, lname, myRank = CASE WHEN fname = @term THEN 4 WHEN fname like @term + '%' THEN 3 WHEN fname like '%' + @term + '%' THEN 2 ELSE.... whatever ranking you want FROM names WHERE fname LIKE '%' + @term + '%' OR lname LIKE '%' + @term + '%' ORDER BY myRank DESC 

Answer 2

You need to study the full text to find the names. A full-text search will allow you to consider the similarities in the match.

Answer 3

something like that:

 where fname like '%' + @searchterm + '%' or lname like '%' + @searchterm + '%' 

Answer 4

You can make ORDER BY fname ASC at the end of your query (if this syntax works for your database statements, it looks like some kind of SQL, so there should be something like that that works). This sorts the names alphabetically, so that Steve appears in front of Stephen (since "n" is missing). I also assume that if you want to find something based on the first letter or the first letters, there is probably an equivalent where you can use M *, and the asterisk will be your wild card. Hope this helps!

Answer 5

I recently ran into a similar issue and used the similarity feature provided by Microsoft Data Data Services . From now on, I have implemented a word-based trigram function. My implementation will match Spongebob Squarepants with Squarepants, Bob.

If you do not own a visual studio (which is required to create assemblies used in the sql server), you can also implement the desired functionality (i.e. matching fuzzy strings) using native T-SQL. Good tools can be found here .

Update: Note. Matching similarity strings does not work very well with incremental searches, i.e. “M” will not match “Mark”. You will have to use other attempts, perhaps a combination of fuzzy string combinations and the classification in "starts with", "contains", etc.