T-SQL - Using Order By clause to return resultset with exact matches first and then partial matches

This article is about using Order By clause with NULLIF to return resultset with exact matches first and then partial matches .

For Example , consider below sample data :

DECLARE @SearchProduct VARCHAR(50) = 'SQL Server'

DECLARE @Tmp TABLE (Id INT , Products VARCHAR(50))
INSERT @Tmp SELECT 2,'SQL Server 2008'
INSERT @Tmp SELECT 1,'SQL Server 2005'
INSERT @Tmp SELECT 3,'SQL Server 2008R2'
INSERT @Tmp SELECT 5,'SQL Server'
INSERT @Tmp SELECT 4,'SQL Server 2012'
INSERT @Tmp SELECT 6,'SSIS'
INSERT @Tmp SELECT 7,'SSRS'
INSERT @Tmp SELECT 8,'SSAS'
SELECT * FROM @Tmp

So to return resultset with Products Like ''SQL Server'' , below query can be used :

SELECT * FROM @Tmp
WHERE Products LIKE '%'+@SearchProduct+'%'

To Order By resultset with exact matches first and then partial matches ,
below query can be used :

SELECT * FROM @Tmp
WHERE Products LIKE '%'+@SearchProduct+'%'
ORDER BY NULLIF(Products,@SearchProduct)

Reference - MSDN Forumn - Transact - SQL