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