Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

Nov 25, 2015
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:

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

SQL Server Showing exact match values top and remaining values next
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

SQL Server Showing exact match values top and remaining values next

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 RSS subscribe by email Subscribe by Email

3 comments :

Mlm Consultancy said...

Dear sir Thanks for sharing this article. This is very Help full to me. You really write very well. I am great fan of your blog, keep writing.

Amol Pathe said...

Sir,Please add article about google adsence

SRINIVAS GAIKWAD said...

sir make some youtube video of .net class

Give your Valuable Comments

Other Related Posts

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.