By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,440 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

Is it possible for sort select query based on input string?

P: 62
Hi Team,

Is it possible to sort select query based on input string?

I am looking for somthing like:

select TOP 20 PREFERRED_NAME from FRS_TABLE where
Lower(PREFERRED_NAME) like Lower('%shar%')

order by PREFERRED_NAME LIKE '%shar%'
Nov 24 '09 #1
Share this Question
Share on Google+
4 Replies


nbiswas
100+
P: 149
Try this:

Some dataset

Expand|Select|Wrap|Line Numbers
  1. declare @t table(name varchar(50))
  2. insert into @t 
  3.     select 'shar166' union all
  4.     select 'shar566' union all
  5.     select 'shar16' union all
  6.     select 'shar76' union all
  7.     select 'shar1' union all
  8.     select 'shar23' union all
  9.     select 'shar168' union all
  10.     select 'shar2' union all
  11.     select 'shar1612' union all
  12.     select 'sharname123' union all
  13.     select 'shar4name' union all
  14.     select 'sharn5ame'
Program

Expand|Select|Wrap|Line Numbers
  1. select top 5 ROW_NUMBER()over (order by name )rn, name from @t where 
  2. Lower(name) like Lower('%shar%') 
Output:

Expand|Select|Wrap|Line Numbers
  1. rn    name
  2. 1    shar1
  3. 2    shar16
  4. 3    shar1612
  5. 4    shar166
  6. 5    shar168
Nov 24 '09 #2

P: 62
thanks Biswas for your response.

Actully I am looking for smthing like this


select top 10 PREFERRED_NAME from PS_ARIBA_PHONE_LIST_TBL where
Lower(PREFERRED_NAME) like '%krishna%'

order by
CASE PREFERRED_NAME
when 'krishna%' THEN 1
when '%krishna%' THEN 2

ELSE 100
END

That means: First it should give name starting from krishna and thn it should give name having krishna

Here the output would be:

Krishna Veerappa
Krishnakumar Padmanabhachar
Krishnamoorthy Sathyamoorthy
Prasanna Krishnan
Nov 24 '09 #3

debasisdas
Expert 5K+
P: 8,127
what the below query returns, are not you getting the desired output.

Expand|Select|Wrap|Line Numbers
  1. select TOP 20 PREFERRED_NAME from FRS_TABLE where
  2. Lower(PREFERRED_NAME) like Lower('%shar%')
  3. order by PREFERRED_NAME  
  4.  
Nov 25 '09 #4

P: 62
Nop Debasis, it will return PREFERRED_NAME in alphabetical. For exmple, out put will be like this:

Ashar
Bishar
Sharath
Sharma
Zashar

I need to have Sharrath & Sharma on the top.
Nov 25 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.