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

[Manikrag]

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%'

[nbiswas]

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

Try this:

Some dataset

declare @t table(name varchar(50))
insert into @t 
	select 'shar166' union all
	select 'shar566' union all
	select 'shar16' union all
	select 'shar76' union all
	select 'shar1' union all
	select 'shar23' union all
	select 'shar168' union all
	select 'shar2' union all
	select 'shar1612' union all
	select 'sharname123' union all
	select 'shar4name' union all
	select 'sharn5ame'

Program

select top 5 ROW_NUMBER()over (order by name )rn, name from @t where 
Lower(name) like Lower('%shar%')

Output:

[B]rn	name[/B]
1	shar1
2	shar16
3	shar1612
4	shar166
5	shar168

[Manikrag]

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

[debasisdas]

what the below query returns, are not you getting the desired output.

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

[Manikrag]

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.