473,320 Members | 2,024 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
4 2608
nbiswas
149 100+
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
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
8,127 Expert 4TB
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
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

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

Similar topics

9
by: Robert Brown | last post by:
If I use _reverse_ wildcard search will it always result in a table scan? Is it possible to get the DB (Oracle or SQL server) to use indexes when doing reverse wildcard match? let's say I have:...
1
by: Derek Tinney | last post by:
Hi, I'm having difficulty building an XLST file that allows me to sort a list of log records. I put together an XSL file that allows me to output a copy of the input file and then I attempted...
6
by: Mark Miller | last post by:
I have a scheduled job that uses different XSL templates to transform XML and save it to disk. I am having problems with the code below. The problem shows up on both my development machine (Windows...
6
by: Terentius Neo | last post by:
Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a select statement? I mean something like this: Select c.number, call procedure( c.number ) as list from table c With best...
7
by: nospam | last post by:
Am I trying to do something that is impossible with access 97? I want to write an address book application. I have exported (from a Casio BOSS organiser) all my contact information and it is...
3
by: A. Respecte | last post by:
I have a list of IP addresses, amongst other fields in a db that I need to sort, however, I am having difficulties accomplishing that, and any help will be appreciated. Here is a sample list of...
0
by: Kaur | last post by:
Hi, I have created a report based on a cross tab query and would like to Sort the column heading of the report based on the sort order of another field. I have three tables called survey...
0
by: Kaur | last post by:
Hi, I have created a report based on a cross tab query and would like to Sort the column heading of the report based on the sort order of another field. I have three tables called survey...
5
by: Nirmala123 | last post by:
hi... I want to sort the table using combobox values. I give the code here. address.html: <html> <head> <title>Add a new entry</title> </head>
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.