Published on: 05 February, 2018

How to order the SQL query result by best match keyword search.

Posted by andy | 274 views 0 likes 0 favourites 0 comments
Tumblr Digg Google Plus

If you develop a website or a program that allows your users to perform a search. When the users perform a search query, you probably want to return the best match result first because it is actually more relevant to the search they need. For example, let says we have a database table contains the following list of product.

In this example, we only allow users to perform a search of the products based on a product code and our program will return the top 5 matched result. If a user performs a search by entering a keyword 100. Our basic SQL Query will be something similar to below.

SELECT TOP 5 * 
FROM Products
WHERE ProductCode LIKE '%200%'
ORDER BY 
PRODUCT CODE

If we use above query, it will return the result based on the product code we entered.

The problem with above result is it returns the product in wrong order. It should return the Product ID 4 first, followed by Product ID 2 and Product ID 1. To fix this issue there are two alternative ways we can do. Those are the solutions.

  • 1. Using CHARINDEX function

    We can use the CHARINDEX function which will return the index found of the search keyword. The lower of the index value is the better best match they are. We can use the following SQL Query.

    SELECT TOP 5 * 
    FROM Products
    WHERE ProductCode LIKE '%200%'
    ORDER BY 
    CHARINDEX('200', ProductCode, 1), ProductCode
    

  • 2. Using WHEN CASE option

    By using WHEN CASE option, we can let the result return based on the lowest case index we specified. Here is the SQL Query.

    SELECT TOP 5 * 
    FROM Products
    WHERE ProductCode LIKE '%200%'
    ORDER BY CASE WHEN ProductCode = '200' THEN 0
    		      WHEN ProductCode LIKE '200' + '%' THEN 1
    		      WHEN ProductCode LIKE '%' + '200' + '%' THEN 2
    		      WHEN ProductCode LIKE '%' + '200' THEN 3
    		      ELSE 4
    		END, ProductCode
    

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.
Share your article with us and add your own google adsense account to earn extra money. Plus, you can promote a link back to your site.
Related Articles
Published on: 12 February, 2018

If you have a situation where you want to add a column into a table in SQL Server, it would be a good idea to perform a check first if a column has already existed in a table. Otherwise, if you try to add a duplicate column, you may get an error message when you try to add a column via SQL query.

Published on: 25 September, 2017
When creating a function dynamically, it is a best practice to perform a check if a particular function you want to create has already existed in a database. To perform a check of an existing function you can use the following EXISTS keyword. If the function does not exist then perform a creation of the function.
Published on: 12 May, 2016
Do you need to sort comments with nested replies in SQL Server? Well this can be easily done by combining the same table using the UNION ALL join to populate the comments content. There is a trick to used to sort the comments which use the combination of a record ID field with the row number index.
Published on: 14 April, 2016
Before running an SQL script to create a table dynamically, it would be a good step if you perform a checking if a table already exists. If it does, you may want to skip creating the table otherwise you will get an error exception.
Related Tutorials
Published on: 27 September, 2013
Learn what is UpperCase function in SQL server and how to use UCASE() in sql query.
Published on: 26 September, 2013
Learn what is LCASE() function in SQL server and how to use LCASE() in sql query.
Published on: 25 September, 2013
Learn what is LEN() function in SQL server and how to use LEN() in sql query.
Published on: 24 September, 2013
Learn what is COUNT() function in SQL server and how to use COUNT() in sql query.
Published on: 23 September, 2013
Learn what is AVG() function in SQL server and how to use AVG() in sql query.