SQL Server Show Exact Match Values Top (First) then Partial Match Values (Like) 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