SQL Server Show Exact Match Values Top (First) then Partial Match Values (Like) Example

Introduction:

Here I will explain how to use sql server to show exact match values at the top and then partial match values with example or sql server show exact match values at first and then show partial match / like values next with example. By using Order By property we can show exact match values first and then remaining values in sql server.

Description:

In previous articles I explained sql server charindex function example, sql server get string before and after delimiter or character,  sql server check if string contains specific word or not, sql server query to read xml data file, single stored procedure to create, update, delete operations in sql server, SQL Server restore database from .bak / .mdf file and many articles relating to SQL server. Now I will explain how to show exact match values at the topic and partial match values next in sql server with example.

To show exact match values at topic and then partial match values next in sql server we can write different queries like as shown below

Method 1

In case our matching parameter is integer means following query will return exact match records top and then remaining topics

DECLARE @temp table(id int, name varchar(50)) insert into @temp(id,name) values(1,'Suresh,Dasari'), (2,'Rohini,Alavala'), (3,'Madhav,Sai'), (13,'Honey'), (21,'Praveen Alavala') DECLARE @temval varchar(30) SET @temval =3 Select id, name from @temp where id like '%'+@temval+'%' order by LEN(id)

When we run above query we will get output like as shown below

Output

Method 2

In case our matching parameter is string means we need to write the query like as shown below

DECLARE @temp table(id int, name varchar(50)) insert into @temp(id,name) values(1,'Suresh,Dasari'), (2,'Rohini,Alavala'), (3,'Madhav,Sai'), (13,'Honey'), (21,'Praveen,Alavala'), (21,'Sateesh,Chandra') DECLARE @temval varchar(30) SET @temval ='sa' Select id, name from @temp where name like '%'+@temval+'%' order by case when name = @temval then 1 when name like @temval+'%' then 2 else 3 end

If we execute above query we can show exact match first and then partial match values and output like as shown below

Output

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email