How to order result accordin to search criteria? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to order result accordin to search criteria?

Author

Topic

sidharth.banyal
Starting Member

11 Posts

Posted - 2010-01-07 : 07:29:05

How can i sort result from select statement accordin to search criteria?

Select * from dbemp where empname like '%'+@searchtext+'%'

Now i need that results which are having @searchtext at begining should be placed above those which are having @searchtext in middle and in same way those having @searchtext in middle should be above those which are having @searchtext in last.

Any help will be highly appreciated.
Thanks in advance.

Sidharth Banyal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-07 : 07:47:34

Select * from dbemp where empname like '%'+@searchtext+'%'
order by charindex('%'+@searchtext+'%' ,empname )

Madhivanan

Failing to plan is Planning to fail

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-07 : 07:49:23

I think this way.

Select * from dbemp where empname like '%'+@searchtext+'%' ORDER BY CHARINDEX( @searchtext, empname, 0) ASC

balaganapathy n.

Anything you can imagine is real.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-07 : 07:52:39

quote:
Originally posted by balaganapathy.n

I think this way.

Select * from dbemp where empname like '%'+@searchtext+'%' ORDER BY CHARINDEX( @searchtext, empname, 0) ASC

balaganapathy n.

Anything you can imagine is real.

You dont need 0 in the charindex
See my previous reply

Madhivanan

Failing to plan is Planning to fail

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-07 : 08:02:27

quote:
Originally posted by madhivanan

quote:
Originally posted by balaganapathy.n

I think this way.

Select * from dbemp where empname like '%'+@searchtext+'%' ORDER BY CHARINDEX( @searchtext, empname, 0) ASC

balaganapathy n.

Anything you can imagine is real.

You dont need 0 in the charindex
See my previous reply

Madhivanan

Failing to plan is Planning to fail

Yes, it is optional.

but, I think it should be only @searchtext instead of '%'+@searchtext+'%' in the CHARINDEX function.

balaganapathy n.

Anything you can imagine is real.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-07 : 08:09:10

quote:
Originally posted by balaganapathy.n

quote:
Originally posted by madhivanan

quote:
Originally posted by balaganapathy.n

I think this way.

Select * from dbemp where empname like '%'+@searchtext+'%' ORDER BY CHARINDEX( @searchtext, empname, 0) ASC

balaganapathy n.

Anything you can imagine is real.

You dont need 0 in the charindex
See my previous reply

Madhivanan

Failing to plan is Planning to fail

Yes, it is optional.

but, I think it should be only @searchtext instead of '%'+@searchtext+'%' in the CHARINDEX function.

balaganapathy n.

Anything you can imagine is real.

Yes. Only in patindex you need it

Madhivanan

Failing to plan is Planning to fail

sidharth.banyal
Starting Member

11 Posts

Posted - 2010-01-07 : 08:30:25

Sorry it is not working some records are still behind those which are having @searchtext in begining.
I tried both solutions but they are not working in desired way

Sidharth Banyal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-07 : 08:46:32

quote:
Originally posted by sidharth.banyal

Sorry it is not working some records are still behind those which are having @searchtext in begining.
I tried both solutions but they are not working in desired way

Sidharth Banyal

Try this

Select * from dbemp where empname like '%'+@searchtext+'%'
order by charindex(@searchtext ,empname ),empname

Madhivanan

Failing to plan is Planning to fail

sidharth.banyal
Starting Member

11 Posts

Posted - 2010-01-07 : 08:51:53

Sorry dear its still not working now it sorting records on empname.

Sidharth Banyal

sidharth.banyal
Starting Member

11 Posts

Posted - 2010-01-07 : 08:55:18

Its not sorting it at all records are stil in same order as they were before in table. is there some other way to do this charindex and patindex not working at all.

Sidharth Banyal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-07 : 08:59:43

quote:
Originally posted by sidharth.banyal

Its not sorting it at all records are stil in same order as they were before in table. is there some other way to do this charindex and patindex not working at all.

Sidharth Banyal

What is the datatype of empname?
Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-07 : 09:05:01

What is about this?


order by 
case when ltrim(rtrim(Empname)) like @searchtext+'%' then 1
     when ltrim(rtrim(Empname)) like '%_'+@searchtext+'_%' then 2
     else 3
end

No, you're never too old to Yak'n'Roll if you're too young to die.

sidharth.banyal
Starting Member

11 Posts

Posted - 2010-01-07 : 22:39:40

order by
case when ltrim(rtrim(Empname)) like @searchtext+'%' then 1
when ltrim(rtrim(Empname)) like '%_'+@searchtext+'_%' then 2
else 3
end
This is the perfect solution. Its working fine.DataType of empname is Varchar.
Thanks to all of you.
Thanks a lot

Sidharth Banyal

sidharth.banyal
Starting Member

11 Posts

Posted - 2010-01-07 : 23:34:29

select * from dbemp
order by case when Charindex(@searchtext,empname ) = 0 then 255
else Charindex(@searchtext,empname)end,empname

also works fine. Problem with charindex is that it returns 0 when no match is found and record are ordered before those which have match.So, we need to put records with charindex 0 behind others. 255 is length of empname column.

But, soluion provided by webfred is a lot better.

Sidharth Banyal

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-08 : 01:20:05

Glad to help

Edit: instead of "Glad I could help" now "Glad to help" because I think "Glad I could help" is wrong...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources