Published on: 05 February, 2018

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

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.